SQL Minute Total by Hour

I have two fields in a data table - "startTime" and "endTime." These two fields represent a duration of time the user spent on a particular task. These are varchar fields. So, let's say we have a startTime of "21:05:00" and an endTime of "22:09:00." I need the code to sum the total number of minutes spent in hour 21, and the 22 hundred hour separately (i.e. 9 minutes). So, not just a simple minute difference, but a breakdown by hour.

What might be the best way to do that?

Thus far, I have created a table that will return all possible hours in a 24-hour period. Here's a sample:

Hour    startTime                endTime
0       2015-01-01 00:00:00.000  2015-01-01 01:00:00.000
1       2015-01-01 01:00:00.000  2015-01-01 02:00:00.000
2       2015-01-01 02:00:00.000  2015-01-01 03:00:00.000

And I have converted the startTime field from varchar to dateteime and called it sessionHour:

Convert(datetime, startTime) As sessionHour

Additionally, I was able to get the hour of the startTime by doing:

DateAdd(Minute, 60 * (DateDiff(Minute, 0, startTime) / 60), 0)  As hourOf

Beyond that, I am lost as to how to parse out the minutes per hour. Help very much appreciated. Thanks!

Stable SQL Server stored procedure unusual drop in performance

I'm experiencing a very strange issue in SQL Server 2005.

Yesterday users reported slowness in a specific part of our database app. I am not sure how pervasive the slowness is - it's definitely not everywhere, as this is the only part of the system reported - but I isolated the relevant stored procedure which used to run in 2-3 seconds and is now consistently running in 50-60 seconds.

It's a complex query -- multiple layers of subqueries. It returns only 42 rows in 16 columns.

The query looks like this:

select col1,2,3,4,5,...
from 
( select .... ) t
ORDER BY col1

I started picking apart the query to find out what was slow and found that removing the final ORDER BY clause brought the performance back in line.

This is highly mysterious. I could not replicate the problem on our DEV server. It's only 42 rows so the order by clause should be inconsequential. Execution plans are identical w/ and without the order by, and on the two servers.

Any brainstorming about what could have changed on our production server would be much appreciated!

SSIS - SQL Server Agent Job not working properly

Before I start, unfortunately I do not have access to the package design as the person who created this package has left and was stored locally on his office computer. However, this package is called "GenericReportExtract", and, as you can probably gather, was designed to be a multipurpose SSIS package which creates an SSRS report and then emails an Excel version to the recipients the report was designed for.

This package has worked perfectly for every single Job it has been used by except one, where, for some reason, will say the Job has run without any errors, but when you look in the output path of this report, it doesn't exist.

But, when I run it manually (i.e. right click job > Start Job at Step), the report is actually produced and you can see it in the output path for the report.

The job itself was run under Admin\SQL_Services, so I thought it maybe didn't like the owner and I changed it to under my log-in, seeming as it worked fine when I manually ran it. However, the next time the job was scheduled to run, the recipient still didn't receive the report and still didn't appear in the output path.

I do not wish to create a new package as there are too many packages that rely on this package, and I suspect it's something to do with the job. It's worth noting that this job ran successfully for about a year until last Christmas, and then in January it stopped doing what it was supposed to do, even though the Job history says it's working fine. I have been manually running the job since, but it does need attending to now.

SQL Server Datetime Conversion In Select Statement

I have a database table that has a date_time column. I created a view where I pull records from the table where the date_time value is in between two dates. For the sake of example, let's say the view is pulling all records between '2015-04-20' AND '2015-04-26'. When I check the min and max dates, however, the min date is 2015-04-19 17:01:49.000

CREATE VIEW [dbo].[testview]
AS
SELECT * FROM table WHERE
EventDateTime BETWEEN '2015-04-20' AND '2015-04-26'
ORDER BY EventDateTime ASC

I have records from January 1, 2014 to April 29, 2015 in this table. When I check the results, they look ok for the most part, except for some reason it keeps pulling a record with a 2015-04-19 17:01:49.000 timestamp. All other recoirds are within the filter. WHY?

The data points being inserted into the database are in Pacific time instead of UTC. No changes are being made, the data is just being generated with a PDT timestamp, and that data is being directly inserted into the database without manipulation.

SQL Server has no concept of a time zone and inherits the system time from Windows. The timezone of the server that this install of SQL Server is on is Central time. Is SQL Server internally converting the times based on it's own system time, i.e. since it's in Central time, it assumes that all data_points are in UTC and then converts to Central time? Instead of searching for date_times above 2015-04-20 00:00:00 is it searching for records where the date_time is above 2015-04-19 18:00:00? Yet if that were the case, the above timestamp still wouldn't work.

Can someone please explain what is happening?

AD query works on SQL 2005 but not on SQL 2008 R2

Note: Active Directory is running on Server 2003 32 bit.

This query works on our current production SQL 2005 32 bit server:

select cn, samaccountname, mail,co, distinguishedName, displayName

from openquery
(ADSI,'SELECT cn,samaccountname, mail, co, distinguishedName, displayName

            FROM ''http://LDAP<ourDomain>''  ')

But when I run it on SQL 2008 R2 64 bit, I get:

Msg 7330, Level 16 State 2, Line 1 Cannot fetch a row from OLE DB provider "AdsDSOObject" for linked server "ADSI".

The linked server connection on the 2008 R2 SQL tests OK, everything is configured the same as SQL 2005. The most common fix I found online said to make sure InProcess is checked in the AdsDSOObject properties, which it is.

Any help greatly appreciated.

MSSQL - Correct way to script a user and role for web application

I'm migrating some old applications to another database server (sql2005) and wanted to revisit the way our web applications access the databases. I plan on using a role for the permissions instead of the user. I've written the following and wanted to make sure I'm scripting this correctly. The applications do not use stored procedures or ORM so I want to limit the role to the basic CRUD operations.

Also, is there any way to set the databasename, rolename and username as variables at the top for reuse in the script? Thanks!

USE [db_name]
GO

CREATE ROLE [db_webrole]
GO

GRANT SELECT, UPDATE, INSERT, DELETE to [db_webrole]
GO

CREATE LOGIN [username] WITH PASSWORD='password', 
       DEFAULT_DATABASE=[db_name], CHECK_POLICY=OFF
GO

CREATE USER [username] FOR LOGIN [username]
EXEC sp_addrolemember N'db_webrole', N'username'
GO

SQL Server WHERE Clause with optional parameter not working as Expected

I have below query. Here Parameter @company is optional from .NET code.

When I pass the value, it is working as expected and bringing matching results for this @company. When i Don't pass the value, it should bring more records for all all other Companies also. Somehow it is not bringing back anything.

I know we can write dynamicSQL and add that condition if it is not null. But is there any other better short cut to do this?

Appreciate your responses.

    declare @company varchar(20) = 'AAA'
select 
    distinct fname as first_name, u.lname as last_name, 
    sc.company as employed_by
        , sc.short_description as employed_by_company_name, u.user_code
    from users u
        inner join sis_companies sc on sc.company = u.company 
            inner join sis_carriers car on sc.company = sc.company
            inner join carrier_shipper_contact_roles cashro on car.carrier = cashro.carrier
    Where cashro.carrier = 'CPL'
      and (isnull(sc.company, '') = '' or sc.company = @company)

Thanks

Oracle ODI JDBC driver for SQL Server 2005 connection error

I am using Oracle ODI 11.1.1.7 version on a Windows 7 64bit machine. I want to connect to a SQL Server 2005 database using the driver com.microsoft.sqlserver.jdbc.SQLServerDriver.

I have downloaded and installed from Microsoft the drivers

• Microsoft JDBC Driver 4.1 for SQL Server

and also

• Microsoft JDBC Driver 4.0 for SQL Server

But I still have a connection error:

ODI-26039: Connection failed

Java.lang.RuntimeException: java.lang.IllegalArgumentException: Could not load JDBC driver class [com.microsoft.sqlserver.jdbc.SQLServerDriver]

Did anyone else experience this issue?

Thank you in advance.

How to get last inserted value of a Specific column in SQL?

Is there any way where we could us IDENT_CURRENT() to retrieve a specific value from a specific column

view as table ms sql 2005 with range od date for each record

I have table "employees" with columns surname, names, birth date etc. What I want to have is a table that for each row in employees table have range of dates for example:

surname0 , day()+0
surname0 , day()+1
surname0 , day()+2
.................
surname0 , day()+30
surname1 , day()+0
surname1 , day()+1
surname1 , day()+2
.................
surname1 , day()+30
.................
.................
surname100 , day()+0
surname100 , day()+1
surname100 , day()+2
.................
surname100 , day()+30

I have no idea how to do that.

The main idea is to have list of employees and the number of their activities per day. In case that particular employee has no task for some specific day to have "0". I have table "tasks" with columns "teams" (consist of several surnames separated by commas), "day", "task", "description" etc. So comparing that table with the one mentioned at the beginning with range of dates per each surname I could get number of task/activities per day per surname. On other way using smth like this

select distinct surname, day, count(surname) over (partition by day, surname) from employees left outer join tasks on team like '%'+surname+'%'

I dont get rows with "0" zero values for all employees that have no tasks for particular day, that is the reason why I want to have that table with range of days and combining that table with tasks table I can easily get list of all employees for following 30 days and number of their tasks.

I work on ms sql 2005 server

thanks

How break a time range between n numbers of equal intervals?

TimeMin: 2015-04-29 10:57:56.623

TimeMax: 2015-04-29 11:04:35.133

I am trying to write a select query to break this into n equal intervals

This is my attempt:

declare @Min int
select @Min  = min(DATEDIFF(ss,'1970-01-01', biddate)) from tbl_bids
declare @Max int
select @Max  = max(DATEDIFF(ss,'1970-01-01', biddate)) from tbl_bids

declare @NumParts int 
select @NumParts =COUNT(*) from tbl_bids

select ((DATEDIFF(ss,'1970-01-01', biddate) * (@Max - @Min) / @NumParts) + 1) - ((@Max - @Min) / @NumParts), 
(DATEDIFF(ss,'1970-01-01', biddate) * (@Max - @Min) / @NumParts) + 1
from tbl_bids 
where DATEDIFF(ss,'1970-01-01', biddate)<= @NumParts

But it returns 0 rows.

EXAMPLE:

Min: 2015-04-29 10:50:00

Max: 2015-04-29 11:00:00

if NumParts =5 (breaking into 5 equal intervals) output should be

2015-04-29 10:52:00
2015-04-29 10:54:00
2015-04-29 10:56:00
2015-04-29 10:58:00
2015-04-29 11:00:00

unable to get that data from sql to display in textbox what's wrong with the code?

i want to input a data from textbox1 (ex: NSB1000-PCP) and when i click textbox2 i want to display the corresponding value of PCP which is PENTAX. i was unable to get that data from sql to display in textbox2? what's wrong with the code? (PCP = abbrev; PENTAX = comp)

Dim conn As SqlConnection Dim adapter As New SqlDataAdapter Dim connecting As String Dim reader As SqlDataReader

    connecting = "Data Source = ERIK\SQLEXPRESS; Integrated Security = True; database = NSB1"
    conn = New SqlConnection(connecting)
    Dim text1 As String = TextBox1.Text
    Dim words() As String
    Dim separator() As String = {"-"}
    words = text1.Split(separator, StringSplitOptions.RemoveEmptyEntries)

    Try
        conn.Open()
        Dim Query As String = words(1)
                   Query = "select comp from list where abbrev = '" & TextBox1.Text & "' "
        COMMAND = New SqlCommand(Query, conn)
        reader = COMMAND.ExecuteReader
        Dim dt As New DataTable
        While reader.Read
            TextBox2.Text = reader("comp")
        End While
        conn.Close()
    Catch ex As Exception

    End Try

Return Data from Table Valued Function with Joins

I have a query which contain table valued function. But when executing this attached error comming. How can I solve this?

SELECT bg.Description, im.ERPItemCode,
(select Opening,Topup1,Topup2,Topup3,
 Returnsv,SaleQty,SaleVal from dbo.GetOpeningByRepAcc(@Date,@RepAccId,@Cente![enter image description here][2]rId,im.Id)),
(select Name from dbo.DistributionCenter where Id = @CenterId) as center,
(select Name from dbo.SalesRepAcc where Id = @RepAccId)as repacc    
FROM BudgetGroup AS bg 
INNER JOIN  ItemMaster AS im ON bg.Id = im.BudgetGroupId
WHERE (bg.Active = 1)

Why I'm getting exception: InvalidOperationException?

The Exception is on the code The code is

string cs=ConfigurationManager.ConnectionStrings["Sam"].ConnectionString;
using(SqlConnection con = new SqlConnection(cs))
{
    SqlCommand cmd = new SqlCommand("Select * from tblProduct", con);
    con.Open();
    SqlDataReader rdr = cmd.ExecuteReader();
    GridView1.DataSource = rdr;
    GridView1.DataBind();
}

So I have a one web-Config file.so I am connect with it. The code inside a web config is

<connectionStrings>
    <add name ="Sam"
    connectionString=" data source=.\\SQLExpress; database=sample; Integrated Security=true " />
</connectionStrings>

After Compiling it there is no error but when I debug it, at the run time I got

An exception of type 'System.InvalidOperationException' occurred in System.Data.dll but was not handled in user code. Additional information: Instance failure.

Why I am getting such error

Stored procedure execution issue

I have few stored procedures at not my MS SQL Server 2005. From my Java project via jdbc I can execute most of them. They did only read from database that's why I used Statement.executeQuery() method. Now I want to insert new data via one of this procedures.

Method Statement.executeQuery() returned me an error sqlserverexception the statement did not return a result set. I found that I have to use Statement.execute(). Now I have no exceptions, but new data wasn't added to database. Also as result I have to get dataset but I can't get it.

Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        JSONObject resultObject = new JSONObject();
        int code = 0;
        try{
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
            String connectionUrl = "jdbc:sqlserver://" + Utils.getServerName() + ":1433;" + "DatabaseName=" + Utils.getDatabaseName(); 
            con = DriverManager.getConnection(connectionUrl, Utils.getUserName(), Utils.getUserPassword());
            ps = con.prepareStatement("EXEC PDA_WorkTimeRegister ?,?,?,?");
            ps.setEscapeProcessing(true);
            ps.setInt(1, workId);
            ps.setInt(2, workerId);
            ps.setTimestamp(3, new java.sql.Timestamp(Utils.fromStringToMilliseconds(startDate)));
            ps.setTimestamp(4, new java.sql.Timestamp(Utils.fromStringToMilliseconds(endDate)));
//          rs = ps.executeQuery();
            ps.execute();
            if(ps.getMoreResults()){
                rs = ps.getResultSet();
                if(rs != null)
                    while(rs.next()){
                        code = rs.getInt("wtr_code");
                    }
            }
        }catch(Exception e){
            e.printStackTrace();
            return Response.status(200).entity(Utils.getErrorJSONString(e.toString())).build();
        }finally{
            try{
                if(rs != null){
                    rs.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
            try{
                if(ps != null){
                    ps.close();
                }
            }catch (Exception e){
                e.printStackTrace();
            }
        }

Stored procedure looks like this.

CREATE PROCEDURE PDA_WorkTimeRegister
(
 @JobID   int,
 @StaffID int,
 @DateFrom DateTime,
 @DateTo   DateTime
) 
as
SELECT
        wtr_code,   -- int,  код строки в таблице(ключ)
        wtr_date = datetime

How can I do it right?

DbUpdateConcurrencyException when inserting a single row

I cannot insert a row in the table defined below. I have to conform to the table´s composite primary key. When using Entity Framework to insert a single row into the table, I get a DbUpdateConcurrencyException. Digging a bit deeper into the exception I see "OriginalValues cannot be used for entities in the Added state."

• I am running EF 6.1.3 database first
• My database is a SQl 2008, set in compatiblity mode 2005.

Below is my code for addding the row as well as the table schema.

c# code:

Entities.Entry(new jotch_krdtrans() {Kred = 1, Regdato = DateTime.Now}).State = EntityState.Added;
Entities.SaveChanges();

SQL table schema:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[jotch_krdtrans](
    [Kred] [decimal](12, 0) NOT NULL,
    [Regdato] [datetime] NOT NULL,
    [Autonum] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_jotch_krdtrans] PRIMARY KEY CLUSTERED 
(
    [Kred] ASC,
    [Regdato] ASC,
    [Autonum] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

I have tried setting the ProviderManifestToken to "2005" in my edmx-file and this seems to make it work. However I would prefer a more detailed explanation why this makes it work. What changed between 2005 and 2008 in relation to my insert example? I am hoping for a deeper understanding.

Thanks in advance.

Multiple rows are not showing when we convert the table data into XML in SQL

I have two table as given below.

OrderHeader

PKOrderHeader CustomerCode DocumentRef SiteCode
1         JOE          TEST1       TH
2         POL          TEST2       CO
3             GEO          TEST3       KH

OrderDetails

FKOrderHeader   ProductCode RotationLineNo
1       PRD1        1
1                         PRD2        2
2               PRD3        2
3               PRD4        3  

I need to get the XML string as below after converting the table data as XML string

< ORDERS>
<SO>
       <HD>
           < PKOrderHeader >1< /PKOrderHeader >
           < CustomerCode > JOE < /CustomerCode >>
       </HD>
       <HO>
           < DocumentRef > TEST1 < / DocumentRef >
           < SiteCode > TH< / SiteCode >>
       </HO>
       <LO>
           < FKOrderHeader >1< FKOrderHeader >
           < ProductCode > PRD1 < ProductCode >
           < RotationLineNo > 1 < RotationLineNo >
       </LO>
       <LO>
           < FKOrderHeader >1< FKOrderHeader >
           < ProductCode > PRD2 < ProductCode >
           < RotationLineNo > 2< RotationLineNo >
       </LO>
</SO>

<SO>
     <HD>
           < PKOrderHeader >2< /PKOrderHeader >
           < CustomerCode > POL < /CustomerCode >>
       </HD>
       <HO>
           < DocumentRef > TEST2 < / DocumentRef >
           < SiteCode > CO< / SiteCode >>
       </HO>
       <LO>
           < FKOrderHeader >2< FKOrderHeader >
           < ProductCode > PRD2 < ProductCode >
           < RotationLineNo > 2 < RotationLineNo >
       </LO>       
</SO>

<SO>
     <HD>
           < PKOrderHeader >3< /PKOrderHeader >
           < CustomerCode > GOE < /CustomerCode >>
       </HD>
       <HO>
           < DocumentRef > TEST3 < / DocumentRef >
           < SiteCode > KH< / SiteCode >>
       </HO>
       <LO>
           < FKOrderHeader >3< FKOrderHeader >
           < ProductCode > PRD3 < ProductCode >
           < RotationLineNo > 3 < RotationLineNo >
       </LO>  
</SO>
< /ORDERS>

The query that I used to generate the XML string is as given

SELECT (SELECT PKOrderHeader, CustomerCode FROM #OrderHeader FOR XML PATH(''), TYPE) AS HD, (SELECT DocumentRef, SiteCode FROM #OrderHeader FOR XML PATH(''), TYPE) AS HO,
(SELECT FKOrderHeader, ProductCode, RotationLineNo FROM #OrderDetail FOR XML PATH(''), TYPE) AS LO FOR XML PATH('SO'), ROOT('ORDERS')

But when I generated the XML string I am getting only the Single rows data as XML string as liken given below. Also the LO section is also not showing the multiple rows.

< ORDERS>
<SO>
       <HD>
           < PKOrderHeader >1< /PKOrderHeader >
           < CustomerCode > JOE < /CustomerCode >>
       </HD>
       <HO>
           < DocumentRef > TEST1 < / DocumentRef >
           < SiteCode > TH< / SiteCode >>
       </HO>
       <LO>
           < FKOrderHeader >1< FKOrderHeader >
           < ProductCode > PRD1 < ProductCode >
           < RotationLineNo > 1 < RotationLineNo >
       </LO>
</SO>
< /ORDERS>

So can anyone help me to get multiple row data as XML string

Duplicated rows in a Join Query SQL Server 2005

Dear I have the following problem. I need to get a list of products from a SQL Server 2005 database. Turns out I should first calculate the stock, which is related to many tables and then make calculations regarding other tables. The problem is that query shows me that I found records separately and have not managed to group (Investigating Google here and in whole, in addition to testing) so I decided to consult:

PD: I leave aside the products that are in the cellar [cod_bodeg] having lower value 10687 to 10000

PD2: The database belongs to a Chilean ERP and they do not provide the documentation of the model. All I know here I researched on my own.

Table Articles (ART)

• NREGUIST ( ID OF ART )

Table Cellar (CHOI)

• NUMREG ( ID OF CELL )
• STK_FISICO ( Stock of Article in that Cell )

Table NOTDE_DB (ARTICLES SELLED)

• NCODART ( INDEX to NREGUIST of ART )
• cantidad ( Qty of Article that is selled in the invoice this is subtracted of the stock for calculate the qty)

Table STOCK

• ARTICULO ( Index to NREGUIST of ART )
• COD_BODEG ( INDEX to NUMREG of CHOI )

Table DSCTO

• ID_ART ( INDEX to NREGUIST of ART )
• DESCTO ( PRICE of ARTICLE )

This is my Query: What advice?

SELECT
    ROUND ( ISNULL( 
    ( 
    SELECT isnull( SUM( STOCK_DB.STK_FISICO ),0 )
        FROM 
            STOCK_DB
        JOIN CHOI_DB 
            ON STOCK_DB.cod_bodeg = CHOI_DB.numreg 
        WHERE 
            STOCK_DB.ARTICULO=ART_DB.nreguist 
            AND STOCK_DB.NUMEMPSTK=1 
            AND CHOI_DB.codigo NOT IN ('B98','B4','B6','B2')
    ) - ISNULL( 
            (
            SELECT 
                SUM(notde_db.cantidad - notde_db.cantdesp) 
            FROM 
                notde_db,notv_db 
            WHERE 
                notde_db.ncodart=ART_DB.nreguist 
                AND notde_db.terminado=0  
                AND notv_db.numreg=notde_db.numrecor 
            GROUP BY notde_db.ncodart 
            ) 
        ,0) , 0
    ), 0) as DISPONIBLE,
    [NOMBRE],
    [NREGUIST],
    [CODIGO],
    [IMPUTABLE],
    [XX],
    [UNIDMED], 
    ROUND([PRECVTA], 0) AS PRECVTA, 
    [NIVEL1], 
    [NIVEL2], 
    [NIVEL3],
    [NIVEL4], 
    [NIVEL5], 
    [NIVEL6], 
    [NIVEL7], 
    [NIVEL8], 
    [NIVEL9], 
    [CLASE1], 
    [CLASE2], 
    [CLASE3], 
    [CLASE4], 
    [ART_DISPON], 
    [OBS],
    ROUND(ISNULL([DESCT_DB].[DESCTO],0) ,0) as PRECIO2
FROM 
     [STOCK_DB], [ART_DB]
LEFT JOIN 
    [DESCT_DB] ON ([DESCT_DB].[IDART] = [ART_DB].[NREGUIST])
WHERE 
    tipo = 1 
    AND clase2 != 'XX' 
    AND clase4 != 'OFF'
    AND ([STOCK_DB].[cod_bodeg] != 10687 OR ( [STOCK_DB].[cod_bodeg] = 10687 AND [DESCT_DB].[DESCTO] > 10000))
ORDER BY 
    DISPONIBLE DESC

Thanks!

Audit Procedures used on SQL Sever DB

I've inherited a DB recently which contains thousands of procs and functions, however most of them are deprecated and no longer in use. I've started adding a piece of code to the procs one at time to notify me if they run, but this process is really quite manual. Is there any way to start an audit, and see which procs run in the next month or two without adding a piece of code to each proc manually?

Thanks, Eric

ASP DataGrid - Must declare the scalar variable "@LCompanyIDInt"

I have an application I'm working on that have multiple grid views on panels, and those panels are selected by a dropdown list. The first one I'll put here works perfectly.

I have a second grid set up exactly the same (I think) as the first. I've changed variable names a bit, and added a few extra rows for stuff, but otherwise things are identical.

On this second grid, I get an error that my first variable (LCompanyIDInt) is not declared. I have no clue why it says that.

I've pasted both the working & non-working code below. Removed a lot of columns for space reasons, but it always bombs on the LCompanyIDInt issue on the 2nd grid, for either inserting or updating. Resetting or cancelling works fine.

WORKING CODE:

    <!-- Total Points Annuity -->       
        <asp:Panel ID="TPAnnuity_Panel" runat="server" visible="true">  
                <asp:GridView ID="TPAnnuity_GridView" AllowSorting="true" AllowPaging="true" Runat="server"
                    DataSourceID="TPAnnuity_SqlDataSource" DataKeyNames="AnnuityTotalPointsID" 
                    AutoGenerateColumns="False" ShowFooter="true" PageSize="20">
                    <Columns>               
                        <asp:TemplateField HeaderText="ID" visible="False" InsertVisible="False" SortExpression="AnnuityTotalPointsID" HeaderStyle-VerticalAlign="Bottom">
                            <ItemTemplate>
                                <asp:Label ID="Label0" runat="server" Text='<%# Bind("AnnuityTotalPointsID") %>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:Label ID="EditAAnnuityTotalPointsID" runat="server" Text='<%# Bind("AnnuityTotalPointsID") %>'></asp:Label>
                            </EditItemTemplate>
                            <ItemStyle HorizontalAlign="Center" />
                        </asp:TemplateField>

                        <asp:TemplateField HeaderText="Company" SortExpression="CompanyName" HeaderStyle-VerticalAlign="Bottom">
                            <ItemTemplate>
                                <asp:Label ID="Label11" runat="server" Text='<%# Bind("CompanyName") %>'></asp:Label>
                            </ItemTemplate>
                            <EditItemTemplate>
                                <asp:DropDownList ID="EditACompanyID" runat="server" DataSource="<%# ddlCompanyDS %>" DataValueField="CompanyID" DataTextField="CompanyName" selectedValue='<%# Bind("CompanyID") %>'></asp:DropDownList>
                            </EditItemTemplate>
                            <FooterTemplate>
                                <asp:DropDownList ID="NewCompanyID" runat="server" DataSource="<%# ddlCompanyDS %>" DataValueField="CompanyID" DataTextField="CompanyName"></asp:DropDownList>
                                <asp:RequiredFieldValidator ID="RequiredFieldValidator11" runat="server" ControlToValidate="NewCompanyID" Display="Dynamic"  ForeColor="" ErrorMessage="You must enter a value. *" Enabled="false"></asp:RequiredFieldValidator>
                            </FooterTemplate>
                            <FooterStyle Wrap="False" />
                        </asp:TemplateField>    

                        <etc I ran out of room> 

                       <asp:TemplateField ShowHeader="False">
                        <ItemTemplate>
                            <asp:LinkButton ID="lbEdit" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit" ></asp:LinkButton>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:LinkButton ID="lbUpdate" runat="server" CausesValidation="False" CommandName="Update" Text="Update" ></asp:LinkButton>
                            <asp:LinkButton ID="lbCancel" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:LinkButton ID="lbInsert" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" ></asp:LinkButton>
                            <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Reset"></asp:LinkButton>
                        </FooterTemplate>
                    </asp:TemplateField>

                    </Columns>
                </asp:GridView>

                <asp:SqlDataSource ID="TPAnnuity_SqlDataSource" Runat="server"
                    SelectCommand="SELECT * FROM tblTotalPointsAnnuity a left outer join tblcompanyinfo ci on a.CompanyID = ci.CompanyID ORDER BY (CASE WHEN CompanyName Is Null then 1 ELSE 0 END), CompanyName, ProductName "
                    InsertCommand="INSERT INTO [tblTotalPointsAnnuity](CompanyID, ProductName, IssueAges, PlanTypeName, AgentFYC, AgentRenewal, ContractPoints, BonusPoints, IncludeInSummary, IncludeInTopPicks, ActiveProduct) VALUES(@CompanyIDInt, @ProductNameText, @IssueAgesText, @PlanTypeNameText, @AgentFYCInt, @AgentRenewalInt, @ContractPointsDec, @BonusPointsInt, @IncludeInSummaryInt, @IncludeInTopPicksInt, @ActiveProductInt) "
                    UpdateCommand="UPDATE [tblTotalPointsAnnuity] Set CompanyID = @CompanyIDInt, ProductName = @ProductNameText, IssueAges = @IssueAgesText, PlanTypeName = @PlanTypeNameText, AgentFYC = @AgentFYCInt, AgentRenewal = @AgentRenewalInt, ContractPoints = @ContractPointsDec, BonusPoints = @BonusPointsInt, IncludeInSummary = @IncludeInSummaryInt, IncludeInTopPicks = @IncludeInTopPicksInt, ActiveProduct = @ActiveProductInt WHERE [AnnuityTotalPointsID] = @AnnuityTotalPointsIDInt">
                    <InsertParameters>
                        <asp:Parameter Name="CompanyIDInt" Type="Int32" />
                    </InsertParameters>
                    <UpdateParameters>
                        <asp:Parameter Name="CompanyIDInt" Type="Int32" />
                    </UpdateParameters>
                </asp:SqlDataSource>
        </asp:Panel>
<!-- END Total Points Annuity -->

WORKING CODE BEHIND

Sub TPAnnuity_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles TPAnnuity_GridView.RowCommand
    Dim TPAnnuity_searchStr As String = TPAnnuity_search_Text.Text

    If TPAnnuity_searchStr = "" Then
        TPAnnuity_SqlDataSource.SelectCommand = "SELECT * FROM tblTotalPointsAnnuity a " & _
                                        " left outer join tblcompanyinfo ci on a.CompanyID = ci.CompanyID " & _
                                        " ORDER BY (CASE WHEN CompanyName Is Null then 1 ELSE 0 END), CompanyName, ProductName "
    Else
        TPAnnuity_SqlDataSource.SelectCommand = "SELECT * FROM tblTotalPointsAnnuity a " & _
                                        " left outer join tblcompanyinfo ci on a.CompanyID = ci.CompanyID " & _
                                    "WHERE 1=1 "

        If TPAnnuity_search_dropdown.SelectedValue = "Company" Then
            TPAnnuity_SqlDataSource.SelectCommand &= " AND ci.CompanyName like '%" & TPAnnuity_search_Text.Text & "%' OR ci.CompanyCode like '%" & TPAnnuity_search_Text.Text & "%'"
        Else If TPAnnuity_search_dropdown.SelectedValue = "CompanyID" Then
            TPAnnuity_SqlDataSource.SelectCommand &= " AND ci.CompanyID = " & TPAnnuity_search_Text.Text
        Else
            TPAnnuity_SqlDataSource.SelectCommand &= " AND " & TPAnnuity_search_dropdown.SelectedValue & " like '%" & TPAnnuity_search_Text.Text & "%' "
        End If
        TPAnnuity_SqlDataSource.SelectCommand &= " ORDER BY (CASE WHEN CompanyName Is Null then 1 ELSE 0 END), CompanyName, ProductName "
    End If

    If e.CommandName = "Cancel" Then
        CType(TPAnnuity_GridView.FooterRow.FindControl("NewCompanyID"), DropDownList).SelectedIndex = 0
        CType(TPAnnuity_GridView.FooterRow.FindControl("NewProductName"), TextBox).Text = ""
        CType(TPAnnuity_GridView.FooterRow.FindControl("NewIssueAges"), TextBox).Text = ""
        CType(TPAnnuity_GridView.FooterRow.FindControl("NewPlanTypeName"), TextBox).Text = ""
        CType(TPAnnuity_GridView.FooterRow.FindControl("NewAgentFYC"), TextBox).Text = ""
        CType(TPAnnuity_GridView.FooterRow.FindControl("NewContractPoints"), TextBox).Text = ""
        CType(TPAnnuity_GridView.FooterRow.FindControl("NewBonusPoints"), TextBox).Text = ""
        CType(TPAnnuity_GridView.FooterRow.FindControl("NewIncludeInSummary"), DropDownList).SelectedIndex = 0
        CType(TPAnnuity_GridView.FooterRow.FindControl("NewIncludeInTopPicks"), DropDownList).SelectedIndex = 0
        CType(TPAnnuity_GridView.FooterRow.FindControl("NewActiveProduct"), DropDownList).SelectedIndex = 0
    ElseIf e.CommandName = "Insert" Then
        TPAnnuity_SqlDataSource.InsertParameters.Clear()

        Dim test1 As New Parameter("CompanyIDInt", TypeCode.Int32)
        Dim test2 As New Parameter("ProductNameText", TypeCode.String)
        Dim test3 As New Parameter("IssueAgesText", TypeCode.String)
        Dim test4 As New Parameter("PlanTypeNameText", TypeCode.String)
        Dim test5 As New Parameter("AgentFYCInt", TypeCode.Int32)
        Dim test6 As New Parameter("AgentRenewalInt", TypeCode.String)
        Dim test7 As New Parameter("ContractPointsDec", TypeCode.Decimal)
        Dim test8 As New Parameter("BonusPointsInt", TypeCode.Decimal)
        Dim test9 As New Parameter("IncludeInSummaryInt", TypeCode.Byte)
        Dim test10 As New Parameter("IncludeInTopPicksInt", TypeCode.Byte)
        Dim test11 As New Parameter("ActiveProductInt", TypeCode.Byte)

        test1.DefaultValue = CType(TPAnnuity_GridView.FooterRow.FindControl("NewCompanyID"), DropDownList).SelectedValue
        test2.DefaultValue = CType(TPAnnuity_GridView.FooterRow.FindControl("NewProductName"), TextBox).Text
        test3.DefaultValue = CType(TPAnnuity_GridView.FooterRow.FindControl("NewIssueAges"), TextBox).Text
        test4.DefaultValue = CType(TPAnnuity_GridView.FooterRow.FindControl("NewPlanTypeName"), TextBox).Text
        test5.DefaultValue = Utils.NumOrNull(CType(TPAnnuity_GridView.FooterRow.FindControl("NewAgentFYC"), TextBox).Text)
        test6.DefaultValue = CType(TPAnnuity_GridView.FooterRow.FindControl("NewAgentRenewal"), TextBox).Text
        test7.DefaultValue = Utils.NumOrNull(CType(TPAnnuity_GridView.FooterRow.FindControl("NewContractPoints"), TextBox).Text)
        test8.DefaultValue = Utils.NumOrNull(CType(TPAnnuity_GridView.FooterRow.FindControl("NewBonusPoints"), TextBox).Text)
        test9.DefaultValue = CType(TPAnnuity_GridView.FooterRow.FindControl("NewIncludeInSummary"), DropDownList).SelectedIndex
        test10.DefaultValue = CType(TPAnnuity_GridView.FooterRow.FindControl("NewIncludeInTopPicks"), DropDownList).SelectedIndex
        test11.DefaultValue = CType(TPAnnuity_GridView.FooterRow.FindControl("NewActiveProduct"), DropDownList).SelectedIndex

        TPAnnuity_SqlDataSource.InsertParameters.Add(test1)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test2)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test3)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test4)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test5)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test6)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test7)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test8)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test9)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test10)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test11)

        TPAnnuity_SqlDataSource.Insert()
    ElseIf e.CommandName = "Update" Then
        TPAnnuity_SqlDataSource.UpdateParameters.Clear()

        Dim param1 As New Parameter("CompanyIDInt", TypeCode.Int32)
        Dim param2 As New Parameter("ProductNameText", TypeCode.String)
        Dim param3 As New Parameter("IssueAgesText", TypeCode.String)
        Dim param4 As New Parameter("PlanTypeNameText", TypeCode.String)
        Dim param5 As New Parameter("AgentFYCInt", TypeCode.Int32)
        Dim param6 As New Parameter("AgentRenewalInt", TypeCode.String)
        Dim param7 As New Parameter("ContractPointsDec", TypeCode.Decimal)
        Dim param8 As New Parameter("BonusPointsInt", TypeCode.Decimal)
        Dim param9 As New Parameter("IncludeInSummaryInt", TypeCode.Int32)
        Dim param10 As New Parameter("IncludeInTopPicksInt", TypeCode.Int32)
        Dim param11 As New Parameter("ActiveProductInt", TypeCode.Int32)
        Dim param12 As New Parameter("AnnuityTotalPointsIDInt", TypeCode.Int32)

        param1.DefaultValue = CType(e.CommandSource.FindControl("EditACompanyID"), DropDownList).SelectedValue
        param2.DefaultValue = CType(e.CommandSource.FindControl("EditAProductName"), TextBox).Text
        param3.DefaultValue = CType(e.CommandSource.FindControl("EditAIssueAges"), TextBox).Text
        param4.DefaultValue = CType(e.CommandSource.FindControl("EditAPlanTypeName"), TextBox).Text
        param5.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditAAgentFYC"), TextBox).Text)
        param6.DefaultValue = CType(e.CommandSource.FindControl("EditAAgentRenewal"), TextBox).Text
        param7.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditAContractPoints"), TextBox).Text)
        param8.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditABonusPoints"), TextBox).Text)
        param9.DefaultValue = CType(e.CommandSource.FindControl("EditAIncludeInSummary"), DropDownList).SelectedIndex
        param10.DefaultValue = CType(e.CommandSource.FindControl("EditAIncludeInTopPicks"), DropDownList).SelectedIndex
        param11.DefaultValue = CType(e.CommandSource.FindControl("EditAActiveProduct"), DropDownList).SelectedIndex
        param12.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditAAnnuityTotalPointsID"), Label).Text)

        TPAnnuity_SqlDataSource.UpdateParameters.Add(param1)
        TPAnnuity_SqlDataSource.UpdateParameters.Add(param2)
        TPAnnuity_SqlDataSource.UpdateParameters.Add(param3)
        TPAnnuity_SqlDataSource.UpdateParameters.Add(param4)
        TPAnnuity_SqlDataSource.UpdateParameters.Add(param5)
        TPAnnuity_SqlDataSource.UpdateParameters.Add(param6)
        TPAnnuity_SqlDataSource.UpdateParameters.Add(param7)
        TPAnnuity_SqlDataSource.UpdateParameters.Add(param8)
        TPAnnuity_SqlDataSource.UpdateParameters.Add(param9)
        TPAnnuity_SqlDataSource.UpdateParameters.Add(param10)
        TPAnnuity_SqlDataSource.UpdateParameters.Add(param11)
        TPAnnuity_SqlDataSource.UpdateParameters.Add(param12)

        TPAnnuity_SqlDataSource.Update()
    End If
End Sub

NOT WORKING CODE

    <!-- Total Points Life -->      
        <asp:Panel ID="TPLife_Panel" runat="server" visible="false">
            <hr />
                <table>
                    <tr>
                        <td>Search on </td>
                        <td>
                            <asp:DropDownList ID="TPLife_search_dropdown" runat="server">
                                <asp:ListItem Text="Company" Value="Company"></asp:ListItem>
                                <asp:ListItem Text="Company ID" Value="CompanyID"></asp:ListItem>
                                <asp:ListItem Text="Product Name" Value="ProductName"></asp:ListItem>
                            </asp:DropDownList>
                        </td>
                        <td> for </td>
                        <td>
                            <asp:TextBox ID="TPLife_search_Text" runat="server"></asp:TextBox>
                        </td>
                        <td>
                            <asp:Button ID="TPLife_search_Button" runat="server" Text="Search" OnClick="TPLife_search_Click" CssClass="buttonstyle" onmouseover="shade(this);" onmouseout="unshade(this);" />
                        </td>
                    </tr>
                </table>    
                <asp:GridView ID="TPLife_GridView" AllowSorting="true" AllowPaging="true" Runat="server"
                    DataSourceID="TPLife_SqlDataSource" DataKeyNames="LifeTotalPointsID" 
                    AutoGenerateColumns="False" ShowFooter="true" PageSize="20">
                    <Columns>               
                    <asp:TemplateField HeaderText="ID" visible="False" InsertVisible="False" SortExpression="LifeTotalPointsID" HeaderStyle-VerticalAlign="Bottom">
                        <ItemTemplate>
                            <asp:Label ID="Label0" runat="server" Text='<%# Bind("LifeTotalPointsID") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:Label ID="EditLLifeTotalPointsID" runat="server" Text='<%# Bind("LifeTotalPointsID") %>'></asp:Label>
                        </EditItemTemplate>
                        <ItemStyle HorizontalAlign="Center" />
                    </asp:TemplateField>

                    <asp:TemplateField HeaderText="Company" SortExpression="CompanyName" HeaderStyle-VerticalAlign="Bottom">
                        <ItemTemplate>
                            <asp:Label ID="Label11" runat="server" Text='<%# Bind("CompanyName") %>'></asp:Label>
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:DropDownList ID="EditLCompanyID" runat="server" DataSource="<%# ddlCompanyDS %>" DataValueField="CompanyID" DataTextField="CompanyName" selectedValue='<%# Bind("CompanyID") %>'></asp:DropDownList>
                        </EditItemTemplate>
                        <FooterTemplate>
                            <asp:DropDownList ID="NewCompanyID" runat="server" DataSource="<%# ddlCompanyDS %>" DataValueField="CompanyID" DataTextField="CompanyName"></asp:DropDownList>
                            <asp:RequiredFieldValidator ID="RequiredFieldValidator12" runat="server" ControlToValidate="NewCompanyID" Display="Dynamic"  ForeColor="" ErrorMessage="You must enter a value. *" Enabled="false"></asp:RequiredFieldValidator>
                        </FooterTemplate>
                        <FooterStyle Wrap="False" />
                    </asp:TemplateField>

                    <etc I ran out of room>

                    <asp:TemplateField ShowHeader="False">
                    <ItemTemplate>
                        <asp:LinkButton ID="lbEdit" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit" ></asp:LinkButton>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:LinkButton ID="lbUpdate" runat="server" CausesValidation="False" CommandName="Update" Text="Update" ></asp:LinkButton>
                        <asp:LinkButton ID="lbCancel" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
                    </EditItemTemplate>
                    <FooterTemplate>
                        <asp:LinkButton ID="lbInsert" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" ></asp:LinkButton>
                        <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel" Text="Reset"></asp:LinkButton>
                    </FooterTemplate>
                </asp:TemplateField>

                    </Columns>
                </asp:GridView>

                <asp:SqlDataSource ID="TPLife_SqlDataSource" Runat="server"
                    SelectCommand="SELECT * FROM tblTotalPointsLife a left outer join tblcompanyinfo ci on a.CompanyID = ci.CompanyID ORDER BY (CASE WHEN CompanyName Is Null then 1 ELSE 0 END), CompanyName, ProductName "
                    InsertCommand="INSERT INTO [tblTotalPointsAnnuity](CompanyID, ProductName, PlanTypeName, StandardAgtFYC, StandardFYCExcess, StandardRenewal, PreferredAgtFYC, PreferredFYCExcess, PreferredRenewal, ContractPoints, BonusPoints, IncludeInSummary, IncludeInTopPicks, ActiveProduct) VALUES(@LCompanyIDInt, @LProductNameText, @LPlanTypeNameText, @LStandardAgtFYCInt, @LStandardFYCExcessInt, @LStandardRenewalInt, @LPreferredAgtFYCInt, @LPreferredFYCExcessInt, @LPreferredRenewalInt, @LContractPointsDec, @LBonusPointsInt, @LIncludeInSummaryInt, @LIncludeInTopPicksInt, @LActiveProductInt) "
                    UpdateCommand="UPDATE [tblTotalPointsAnnuity] Set CompanyID = @LCompanyIDInt, ProductName = @LProductNameTest = @LProductNameText, IssueAges = @LIssueAgesText, PlanTypeName = @LPlanTypeNameText, StandardAgtFYC = @LStandardAgtFYCInt, StandardFYCExcess = @LStandardFYCExcessInt, StandardRenewal = @LStandardRenewalInt, PreferredAgtFYC = @LPreferredAgtFYCInt, PreferredFYCExcess = @LPreferredFYCExcessInt, PreferredRenewal = @LPreferredRenewalInt, ContractPoints = @LContractPointsDec, BonusPoints = @LBonusPointsInt, IncludeInSummary = @LIncludeInSummaryInt, IncludeInTopPicks = @LIncludeInTopPicksInt, ActiveProduct = @LActiveProductInt WHERE [LifeTotalPointsID] = @LLifeTotalPointsIDInt">
                    <InsertParameters>
                        <asp:Parameter Name="LCompanyIDInt" Type="Int32" />
                    </InsertParameters>
                    <UpdateParameters>
                        <asp:Parameter Name="LCompanyIDInt" Type="Int32" />
                    </UpdateParameters>
                </asp:SqlDataSource>
        </asp:Panel>
<!-- END Total Points Life -->

NOT WORKING CODE BEHIND

Sub TPLife_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles TPLife_GridView.RowCommand
    Dim TPLife_searchStr As String = TPLife_search_Text.Text

    If TPLife_searchStr = "" Then
        TPLife_SqlDataSource.SelectCommand = "SELECT * FROM tblTotalPointsLife a " & _
                                        " left outer join tblcompanyinfo ci on a.CompanyID = ci.CompanyID " & _
                                        " ORDER BY (CASE WHEN CompanyName Is Null then 1 ELSE 0 END), CompanyName, ProductName "
    Else
        TPLife_SqlDataSource.SelectCommand = "SELECT * FROM tblTotalPointsLife a " & _
                                        " left outer join tblcompanyinfo ci on a.CompanyID = ci.CompanyID " & _
                                    "WHERE 1=1 "

        If TPLife_search_dropdown.SelectedValue = "Company" Then
            TPLife_SqlDataSource.SelectCommand &= " AND ci.CompanyName like '%" & TPLife_search_Text.Text & "%' OR ci.CompanyCode like '%" & TPLife_search_Text.Text & "%'"
        Else If TPLife_search_dropdown.SelectedValue = "CompanyID" Then
            TPLife_SqlDataSource.SelectCommand &= " AND ci.CompanyID = " & TPLife_search_Text.Text
        Else
            TPLife_SqlDataSource.SelectCommand &= " AND " & TPLife_search_dropdown.SelectedValue & " like '%" & TPLife_search_Text.Text & "%' "
        End If
        TPLife_SqlDataSource.SelectCommand &= " ORDER BY (CASE WHEN CompanyName Is Null then 1 ELSE 0 END), CompanyName, ProductName "
    End If

    If e.CommandName = "Cancel" Then
        CType(TPLife_GridView.FooterRow.FindControl("NewCompanyID"), DropDownList).SelectedIndex = 0
        CType(TPLife_GridView.FooterRow.FindControl("NewProductName"), TextBox).Text = ""
        CType(TPLife_GridView.FooterRow.FindControl("NewPlanTypeName"), TextBox).Text = ""
        CType(TPLife_GridView.FooterRow.FindControl("NewStandardAgtFYC"), TextBox).Text = ""
        CType(TPLife_GridView.FooterRow.FindControl("NewStandardFYCExcess"), TextBox).Text = ""
        CType(TPLife_GridView.FooterRow.FindControl("NewStandardRenewal"), TextBox).Text = ""
        CType(TPLife_GridView.FooterRow.FindControl("NewPreferredAgtFYC"), TextBox).Text = ""
        CType(TPLife_GridView.FooterRow.FindControl("NewPreferredFYCExcess"), TextBox).Text = ""
        CType(TPLife_GridView.FooterRow.FindControl("NewPreferredRenewal"), TextBox).Text = ""
        CType(TPLife_GridView.FooterRow.FindControl("NewContractPoints"), TextBox).Text = ""
        CType(TPLife_GridView.FooterRow.FindControl("NewBonusPoints"), TextBox).Text = ""
        CType(TPLife_GridView.FooterRow.FindControl("NewIncludeInSummary"), DropDownList).SelectedIndex = 0
        CType(TPLife_GridView.FooterRow.FindControl("NewIncludeInTopPicks"), DropDownList).SelectedIndex = 0
        CType(TPLife_GridView.FooterRow.FindControl("NewActiveProduct"), DropDownList).SelectedIndex = 0
    ElseIf e.CommandName = "Insert" Then
        TPLife_SqlDataSource.InsertParameters.Clear()

        Dim test1 As New Parameter("LCompanyIDInt", TypeCode.Int32)
        Dim test2 As New Parameter("LProductNameText", TypeCode.String)
        Dim test3 As New Parameter("LPlanTypeNameText", TypeCode.String)
        Dim test4 As New Parameter("LStandardAgtFYCInt", TypeCode.Int32)
        Dim test5 As New Parameter("LStandardFYCExcessInt", TypeCode.Int32)
        Dim test6 As New Parameter("LStandardRenewalInt", TypeCode.Int32)
        Dim test7 As New Parameter("LPreferredAgtFYCInt", TypeCode.Int32)
        Dim test8 As New Parameter("LPreferredFYCExcessInt", TypeCode.Int32)
        Dim test9 As New Parameter("LPreferredRenewalInt", TypeCode.Int32)
        Dim test10 As New Parameter("LContractPointsDec", TypeCode.Decimal)
        Dim test11 As New Parameter("LBonusPointsInt", TypeCode.Decimal)
        Dim test12 As New Parameter("LIncludeInSummaryInt", TypeCode.Byte)
        Dim test13 As New Parameter("LIncludeInTopPicksInt", TypeCode.Byte)
        Dim test14 As New Parameter("LActiveProductInt", TypeCode.Byte)

        test1.DefaultValue = CType(TPLife_GridView.FooterRow.FindControl("NewCompanyID"), DropDownList).SelectedValue
        test2.DefaultValue = CType(TPLife_GridView.FooterRow.FindControl("NewProductName"), TextBox).Text
        test3.DefaultValue = CType(TPLife_GridView.FooterRow.FindControl("NewPlanTypeName"), TextBox).Text
        test4.DefaultValue = Utils.NumOrNull(CType(TPLife_GridView.FooterRow.FindControl("NewStandardAgtFYC"), TextBox).Text)
        test5.DefaultValue = Utils.NumOrNull(CType(TPLife_GridView.FooterRow.FindControl("NewStandardFYCExcess"), TextBox).Text)
        test6.DefaultValue = Utils.NumOrNull(CType(TPLife_GridView.FooterRow.FindControl("NewStandardRenewal"), TextBox).Text)
        test7.DefaultValue = Utils.NumOrNull(CType(TPLife_GridView.FooterRow.FindControl("NewPreferredAgtFYC"), TextBox).Text)
        test8.DefaultValue = Utils.NumOrNull(CType(TPLife_GridView.FooterRow.FindControl("NewPreferredFYCExcess"), TextBox).Text)
        test9.DefaultValue = Utils.NumOrNull(CType(TPLife_GridView.FooterRow.FindControl("NewPreferredRenewal"), TextBox).Text)
        test10.DefaultValue = Utils.NumOrNull(CType(TPLife_GridView.FooterRow.FindControl("NewContractPoints"), TextBox).Text)
        test11.DefaultValue = Utils.NumOrNull(CType(TPLife_GridView.FooterRow.FindControl("NewBonusPoints"), TextBox).Text)
        test12.DefaultValue = CType(TPLife_GridView.FooterRow.FindControl("NewIncludeInSummary"), DropDownList).SelectedIndex
        test13.DefaultValue = CType(TPLife_GridView.FooterRow.FindControl("NewIncludeInTopPicks"), DropDownList).SelectedIndex
        test14.DefaultValue = CType(TPLife_GridView.FooterRow.FindControl("NewActiveProduct"), DropDownList).SelectedIndex

        TPAnnuity_SqlDataSource.InsertParameters.Add(test1)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test2)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test3)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test4)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test5)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test6)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test7)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test8)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test9)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test10)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test11)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test12)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test13)
        TPAnnuity_SqlDataSource.InsertParameters.Add(test14)

        TPLife_SqlDataSource.Insert() ***FAILS ON THIS LINE***
    ElseIf e.CommandName = "Update" Then
        TPLife_SqlDataSource.UpdateParameters.Clear()

        Dim param1 As New Parameter("LCompanyIDInt", TypeCode.Int32)
        Dim param2 As New Parameter("LProductNameText", TypeCode.String)
        Dim param3 As New Parameter("LPlanTypeNameText", TypeCode.String)
        Dim param4 As New Parameter("LStandardAgtFYCInt", TypeCode.Int32)
        Dim param5 As New Parameter("LStandardFYCExcessInt", TypeCode.Int32)
        Dim param6 As New Parameter("LStandardRenewalInt", TypeCode.Int32)
        Dim param7 As New Parameter("LPreferredAgtFYCInt", TypeCode.Int32)
        Dim param8 As New Parameter("LPreferredFYCExcessInt", TypeCode.Int32)
        Dim param9 As New Parameter("LPreferredRenewalInt", TypeCode.Int32)
        Dim param10 As New Parameter("LContractPointsDec", TypeCode.Decimal)
        Dim param11 As New Parameter("LBonusPointsInt", TypeCode.Decimal)
        Dim param12 As New Parameter("LIncludeInSummaryInt", TypeCode.Byte)
        Dim param13 As New Parameter("LIncludeInTopPicksInt", TypeCode.Byte)
        Dim param14 As New Parameter("LActiveProductInt", TypeCode.Byte)
        Dim param15 As New Parameter("LLifeTotalPointsIDInt", TypeCode.Int32)

        param1.DefaultValue = CType(e.CommandSource.FindControl("EditLCompanyID"), DropDownList).SelectedValue
        param2.DefaultValue = CType(e.CommandSource.FindControl("EditLProductName"), TextBox).Text
        param3.DefaultValue = CType(e.CommandSource.FindControl("EditLPlanTypeName"), TextBox).Text
        param4.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditLStandardAgtFYC"), TextBox).Text)
        param5.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditLStandardFYCExcess"), TextBox).Text)
        param6.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditLStandardRenewal"), TextBox).Text)
        param7.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditLPreferredAgtFYC"), TextBox).Text)
        param8.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditLPreferredFYCExcess"), TextBox).Text)
        param9.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditLPreferredRenewal"), TextBox).Text)
        param10.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditLContractPoints"), TextBox).Text)
        param11.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditLBonusPoints"), TextBox).Text)
        param12.DefaultValue = CType(e.CommandSource.FindControl("EditLIncludeInSummary"), DropDownList).SelectedIndex
        param13.DefaultValue = CType(e.CommandSource.FindControl("EditLIncludeInTopPicks"), DropDownList).SelectedIndex
        param14.DefaultValue = CType(e.CommandSource.FindControl("EditLActiveProduct"), DropDownList).SelectedIndex
        param15.DefaultValue = Utils.NumOrNull(CType(e.CommandSource.FindControl("EditLLifeTotalPointsID"), Label).Text)

        TPAnnuity_SqlDataSource.InsertParameters.Add(param1)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param2)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param3)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param4)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param5)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param6)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param7)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param8)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param9)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param10)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param11)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param12)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param13)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param14)
        TPAnnuity_SqlDataSource.InsertParameters.Add(param15)

        TPLife_SqlDataSource.Update() ***FAILS ON THIS LINE***
    End If
End Sub

I've tried comparing both front & back codes, and can't see the issue. I've tried making the 2nd panel visible by default, and it doesn't change anything. The reset function works fine on the 2nd grid, but the insert and update commands don't.

Hot to fix ExecuteNonQuery: CommandText property has not been initialized?

I am getting an error , "ExecuteNonQuery: CommandText property has not been initialized" , Please help !!! I have been executing this code to save data in my other forms with our any error but when i try to use this with image save its giving me this error.

 Sub Save_Employee()
    Dim conn As New SqlConnection(ConnectionString)
    Dim comm As SqlCommand = New SqlCommand()
    comm.Connection = conn
    conn.Open()

    Dim str As String = "INSERT INTO Employee_Tbl VALUES (@RegNo,@FullName,@Country,@Occupation,@WorkSite,@DOB,@Photo,@Remarks,@Status)"
    comm.Parameters.AddWithValue("@RegNo", RegNo_TextBox.Text)
    comm.Parameters.AddWithValue("@FullName", FullName_TextBox.Text)
    comm.Parameters.AddWithValue("@Country", Country_ComboBox.Text)
    comm.Parameters.AddWithValue("@Occupation", Occupation_ComboBox.Text)
    comm.Parameters.AddWithValue("@WorkSite", WorkSite_TextBox.Text)
    comm.Parameters.AddWithValue("@DOB", DOB_DateTimePicker.Text)

    Try
        Dim ms As New System.IO.MemoryStream
        Dim bmpImage As New Bitmap(myimage.Image)

        bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
        bytImage = ms.ToArray()
        ms.Close()
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

    comm.Parameters.AddWithValue("@Photo", bytImage)
    comm.Parameters.AddWithValue("@Remarks", Remarks_TextBox.Text)
    comm.Parameters.AddWithValue("@Status", Status_ComboBox.Text)
    comm.ExecuteNonQuery()
    MessageBox.Show("Saved successful!", "Employee Created", MessageBoxButtons.OK, MessageBoxIcon.Information)
    comm.ExecuteNonQuery()
    conn.Close()
End Sub

Will face any sharepoint issue after migrating SQL Server 2005 sp2 to sp4

We have one critical challenge to face on upgradation mechanism. Here I request you to have a change in Production server and need to upgrade from sql server 2005 sp2 to sp4 inorder to fix the bugs as per client request. After a while customer raised one concern regarding shall we face any issue on sharepoint services (due to application hosted on sharepoint services) after upgrading SQL server 2005 SP2 to SP4? currently database servers are sql server 2005 sp2.

It would be helpful to give any ideas on this issue ?

Thanks, Chaitu

Linq query with c#

I have two tables categories and products, and I have to create a linq query that returns me the first 2 lines of products in each category.

Thanks for the help

How to achieve passing dropdown list parameter to filter SSRS report with two options

I am new to SSRS. I developed a report and added few calculated fields. It is showing all the data fine. Now I want to add filter using dropdown list parameters. The way I want to do is when user select SHORTNAME from the list, all the names from NAME column with length less than 6 are displayed and if the users select LONGNAME from the list, all the names from NAME column with length longer than 6 are shown. Please suggest how can I do this?

date between fdate and tdate based on condition in sql sever

have small doubt in sql server please tell me how to solve

Table:Patient (MM-dd-yy) (MM-dd-yy) (MM-dd-yy) pn | rvs |prcode |date |amount |datefdate| datetdate| entry 61 | NULL |MS001 |NULL |NULL |07-23-14| 07-23-14| b 61 | NULL |MS001 |NULL |NULL |08-04-14| 08-04-14| b 61 | NULL |MS001 |NULL |NULL |08-11-14| 08-11-14| b 61 | NULL |MS001 |NULL |NULL |08-20-14| 08-20-14| b 61 | NULL |MS001 |NULL |NULL |08-26-14| 08-26-14| b 61 | NULL |MS001 |NULL |NULL |09-03-14| 09-03-14| b 61 | |00000 | | |01-01-80| 10-06-14| b 61 | |00000 | | |01-01-80| 10-06-14| b 61 | |MS001 | | |01-06-15| 01-06-15| b 61 | 97124 |MS001 |01-06-15|120.00|NULL | NULL | c 61 | 97124 |MS001 |07-23-14|120.00|NULL | NULL | c 61 | 97124 |MS001 |08-04-14|120 | | | c 61 | 97124 |MS001 |08-11-14|120 | | | c 61 | 97124 |MS001 |08-20-14|120 | | | c 61 | 97124 |MS001 |08-26-14|120 | | |c 61 | 97124 |MS001 |09-03-14|120 | | | c 61 | 97124 |MS001 |09-15-14|75 | | | c 61 | 97124 |MS001 |09-15-14|0 | | |c 61 | 60MIN |MS001 |10-27-14|75 | | | c 61 | 60MIN |MS001 |11-04-14|75 | | | c 61 | 60MIN |MS001 |11-10-14|75 | | | c 61 | 60MIN |MS001 |11-25-14|75 | | | c 61 | 60MIN |MS001 |12-02-14|75 | | | c 61 | 60MIN |MS001 |12-09-14|75 | | | c 61 | 60MIN |MS001 |12-15-14|75 | | | c 61 | 60MIN |MS001 |12-22-14|75 | | | c 61 | 60MIN |MS001 |12-30-14|75 | | | c Here I want comapare entry type= c records date data between datefdate and datetdate with entry type b records if this condition satisfy then output we retrive entry type =c records related data and one newcolumns is added to identify records status records in new column if condition not satisfy then we retrive entry type=c records related data and new columns status is "unbilled" Finaly every time we need to compare entry type=c records date is between datefdate and datetdate with entry type B records. if condition satisfy then that records billed other wise unbilled. I tried like below query

SELECT distinct [a].[pn] ,a.rvs,a.date,a.prcode,a.amount, b.datefdate,b.datetdate , CASE WHEN (CONVERT(VARCHAR(10), CAST(a.date AS DATE), 120) between CONVERT(VARCHAR(10), CAST(ltrim(rtrim(b.datefdate)) AS DATE), 120) and CONVERT(VARCHAR(10), CAST(ltrim(rtrim(b.datetdate)) AS DATE), 120) ) THEN 'billed' WHEN (CONVERT(VARCHAR(10), CAST(a.date AS DATE), 120) not between CONVERT(VARCHAR(10), CAST(ltrim(rtrim(b.datefdate)) AS DATE), 120) and CONVERT(VARCHAR(10), CAST(ltrim(rtrim(b.datetdate)) AS DATE), 120) ) THEN 'unbilled' END AS [Filter] from testbilled a join testbilled b on a.pn='61' where a.entry='c' and b.entry='b'

its not give correct result. it is multiplay9*18=144 records commening.

i want output like below:

pn rvs date bcharge entry prcode filter 61 60MIN 09-15-14 75.00 C MS001 Billed 61 60MIN 10-27-14 75.00 C MS001 UnBilled 61 60MIN 11-04-14 75.00 C MS001 UnBilled 61 60MIN 11-10-14 75.00 C MS001 UnBilled 61 60MIN 11-25-14 75.00 C MS001 UnBilled 61 60MIN 12-02-14 75.00 C MS001 UnBilled 61 60MIN 12-09-14 75.00 C MS001 UnBilled 61 60MIN 12-15-14 75.00 C MS001 UnBilled 61 60MIN 12-22-14 75.00 C MS001 UnBilled 61 60MIN 12-30-14 75.00 C MS001 UnBilled 61 97124 01-06-15 120.00 C MS001 Billed 61 97124 07-23-14 120.00 C MS001 Billed 61 97124 08-04-14 120.00 C MS001 Billed 61 97124 08-11-14 120.00 C MS001 Billed 61 97124 08-20-14 120.00 C MS001 Billed 61 97124 08-26-14 120.00 C MS001 Billed 61 97124 09-03-14 120.00 C MS001 Billed 61 97124 09-15-14 0.00 C MS001 Billed

please tell me how to achieve this issue in sql server.

mdf files too big in SQL Server 2005

I hope someone can advise me on what to do here.

I am running my database on SQL Server 2005. I had created a backup maintenance job myself using the SQL Server Management Studio, when using a maintenance plan I set up this to delete files based on the age of the file at task run time older then 1 week.

However some one else wanted to use MOM and do the back up there and this I think is appending the backup each day which now leads to the back up for this one database being 4,350,784 KB and this is causing problems with my database load.

I want to either be able to set up something in MOM for the database backup to delete anything over 1 week old or stop this backup altogether and go back to using Management Studio.

Can anyone advise me on what is the best to do and what to do if I do go back to using Management Studio as how do I can decrease the size of the .mdf file as I have already tried to shrink and now this is also hanging on me??

Please help.

Thanks

MSSQL - want a column to be nullable only for existing and newly created rows

Is it possible to make a column that can only be null from the beginning, but once it has been set to a non-null value then it can never be reset to null?

I'm guessing it might be possible with an update constraint using both the inserted and deleted table, but that seems somewhat complicated given the goal of the task.

Bonus question: Since it doesn't seem easy to do this, is this a sign of bad design (on the part of us / MSSQL)?

date between fdate and tdate based on condition in sql server

Hi Friends I have small doubt in sql server please tell me how to sovle

Table:Patient (MM-dd-yy) (MM-dd-yy) (MM-dd-yy) pn | rvs |prcode |date |amount |datefdate| datetdate| entry 61 | NULL |MS001 |NULL |NULL |07-23-14| 07-23-14| b 61 | NULL |MS001 |NULL |NULL |08-04-14| 08-04-14| b 61 | NULL |MS001 |NULL |NULL |08-11-14| 08-11-14| b 61 | NULL |MS001 |NULL |NULL |08-20-14| 08-20-14| b 61 | NULL |MS001 |NULL |NULL |08-26-14| 08-26-14| b 61 | NULL |MS001 |NULL |NULL |09-03-14| 09-03-14| b 61 | |00000 | | |01-01-80| 10-06-14| b 61 | |00000 | | |01-01-80| 10-06-14| b 61 | |MS001 | | |01-06-15| 01-06-15| b 61 | 97124 |MS001 |01-06-15|120.00|NULL | NULL | c 61 | 97124 |MS001 |07-23-14|120.00|NULL | NULL | c 61 | 97124 |MS001 |08-04-14|120 | | | c 61 | 97124 |MS001 |08-11-14|120 | | | c 61 | 97124 |MS001 |08-20-14|120 | | | c 61 | 97124 |MS001 |08-26-14|120 | | | c 61 | 97124 |MS001 |09-03-14|120 | | | c 61 | 97124 |MS001 |09-15-14|75 | | | c 61 | 97124 |MS001 |09-15-14|0 | | | c 61 | 60MIN |MS001 |10-27-14|75 | | | c 61 | 60MIN |MS001 |11-04-14|75 | | | c 61 | 60MIN |MS001 |11-10-14|75 | | | c 61 | 60MIN |MS001 |11-25-14|75 | | | c 61 | 60MIN |MS001 |12-02-14|75 | | | c 61 | 60MIN |MS001 |12-09-14|75 | | | c 61 | 60MIN |MS001 |12-15-14|75 | | | c 61 | 60MIN |MS001 |12-22-14|75 | | | c 61 | 60MIN |MS001 |12-30-14|75 | | | c

Here I want comapare entry type= c records date information between datefdate and datetdate when entry type=B if this condition satisfy then output we retrive entry type =c records related data and one columns is added to identify records t we pass "valid" records in new column if condition not satisfy then we retrive entry type=c records related data and new columns status is "invalid" I tried like below query SELECT [a].[pn] ,a.amount,a.date, cASE WHEN CONVERT(VARCHAR(10), CAST(a.date AS DATE), 120) <= CONVERT(VARCHAR(10), CAST(ltrim(rtrim(b.datefdate)) AS DATE), 120) AND CONVERT(VARCHAR(10), CAST(a.date AS DATE), 120) >= CONVERT(VARCHAR(10), CAST(ltrim(rtrim(b.datetdate)) AS DATE), 120) THEN 'billed' WHEN CONVERT(VARCHAR(10), CAST(a.date AS DATE), 120) <> CONVERT(VARCHAR(10), CAST(ltrim(rtrim(b.datefdate)) AS DATE), 120) AND CONVERT(VARCHAR(10), CAST(a.date AS DATE), 120) <> CONVERT(VARCHAR(10), CAST(ltrim(rtrim(b.datetdate)) AS DATE), 120) THEN 'unbilled' END AS [Filter] FROM [dbo].testbilled [a] JOIN [dbo].testbilled [b] ON [a].[pn] = [b].[Pn] where a.entry='b' or b.entry='c'

its not give correct result.

i want output like below: pn rvs date bcharge entry prcode filter 61 60MIN 09-15-14 75.00 C MS001 Billed 61 60MIN 10-27-14 75.00 C MS001 UnBilled 61 60MIN 11-04-14 75.00 C MS001 UnBilled 61 60MIN 11-10-14 75.00 C MS001 UnBilled 61 60MIN 11-25-14 75.00 C MS001 UnBilled 61 60MIN 12-02-14 75.00 C MS001 UnBilled 61 60MIN 12-09-14 75.00 C MS001 UnBilled 61 60MIN 12-15-14 75.00 C MS001 UnBilled 61 60MIN 12-22-14 75.00 C MS001 UnBilled 61 60MIN 12-30-14 75.00 C MS001 UnBilled 61 97124 01-06-15 120.00 C MS001 Billed 61 97124 07-23-14 120.00 C MS001 Billed 61 97124 08-04-14 120.00 C MS001 Billed 61 97124 08-11-14 120.00 C MS001 Billed 61 97124 08-20-14 120.00 C MS001 Billed 61 97124 08-26-14 120.00 C MS001 Billed 61 97124 09-03-14 120.00 C MS001 Billed 61 97124 09-15-14 0.00 C MS001 Billed

please tell me how to achivet this issue in sql server.

how to convert date in English and French

I am trying to update date in my tables, so I have date as 20150621 and I want it as June 21, 2015 and 21 juin 2015 (for French).

I have a query: convert(varchar(12),cast([datecolumn]) as datetime),107)

So its changing to: Jun 21,2015 , but I need June 21,2015 and also French date.

I am using Sql 2005, so format doesn`t work in it.

Query to check if record has existed for more than X hours

Here's a sample data.

ID | Date
---------
1  | 4/21/2015 11:00:00 AM
1  | 4/21/2015 01:00:00 PM

Let's say it's currently 2 PM, I need to query ID number 1 only if the time difference between the Column Date and Now is >= 2 hours.

Select ID from Table where datediff(hour, Date, getdate()) >= 2 and ID = '1'

Now this query would return the 1st record with the 11 AM time, but I want to ignore other records and just check if the latest record has existed for 2 or more hours. How should I change my query so that I will not get any results if the current time is 2 PM and my last record is 1 PM.

Is it wise to use triggers as part of an import routine

Hi all I have a requirement to create a web based application using SQL server 2005. The data is coming from a third party source in a text format. This is my idea so far.

1. I have a file system watcher looking for a file in a directory
2. I loop through the file found, find the columns and insert the data one by one in a table
3. Once all data has been inserted, run a stored procedure against the table to do some more cleaning and create totals used within the web app

As you can see there are mainly 2 steps involved within the import after the file has been found. Those are storing data in SQL server and the second to clear up values and do some other work within my database. My question is if as I am looping through the values anyway can I have a trigger (and yes I do know that a trigger is per execution not for every row) to do the cleaning for me as I insert the records in my table.

For example I loop through one by one figure out the columns and then insert them into the table. As that happens a trigger is fired to runs some script (possibly stored procedures)to do some other work on other tables. That way all my file system watch needs to do is get the data and insert them into the table. The trigger will do all the other work. Is this advisable and what will happen if a trigger is already running a script and it is called again by another insert to the table?

Sorry for the long question

Thanks

Where to get MS SQL Server 2005 Software?

I own a software that uses MS-SQL Server 2005 with a runtime licence. The MS-SQL Server crashed and I need setup files. Where can I get/buy the software. What proof should I provide that I own a runtime licence. (The place that I bought the software dowsn't exist any more)

asp.net repeater "eval" not reading value from table containong 200 records?

My problem is the "place": '<%# Eval("placer") %>' in the repeater is not extracting data from table_quake(column: placer). the column placer in the quake table contain 200 records and all 200 record is fetch and place in the repeater. All eval works but only place is not extracting

notes: When i fetch little record like 10 the '<%# Eval("placer") %>' work fine but with 200 records it does not extract.

My sample data for the column placer is:

placer
6km WNW of The Geysers, California
10km S of Anza, California
19km SW of North Nenana, Alaska
 ...........
 ......

here is my asp.net script to create the repeater:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="earthquakemap.aspx.vb" Inherits="earthquakemap" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://ift.tt/kkyg93">

<html xmlns="http://ift.tt/lH0Osb">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <script src="http://ift.tt/11czYwS" type="text/javascript"></script>
     <script src="http://ift.tt/1JVNPKo" type="text/javascript" ></script>
    <script type="text/javascript">
    var markers = [
    <asp:Repeater ID="rptMarkers" runat="server">
    <ItemTemplate>
             {
                "times": '<%# Eval("times") %>',
                "lat": '<%# Eval("latitude") %>',
                "lng": '<%# Eval("longitude") %>',
                "dep": '<%# Eval("depth") %>',
                "magni": '<%# Eval("magnitude") %>',
                "magtype": '<%# Eval("mag_type") %>',


                "place": '<%# Eval("placer") %>'


                "typo": '<%# Eval("type") %>',

            }
    </ItemTemplate>
    <SeparatorTemplate>
        ,
    </SeparatorTemplate>
    </asp:Repeater>
    ];
    </script>

My code behind:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Me.IsPostBack Then
            Dim dt As DataTable = Me.GetData("select * from table_quake")
            rptMarkers.DataSource = dt
            rptMarkers.DataBind()



        End If
    End Sub

    Private Function GetData(ByVal query As String) As DataTable
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Dim cmd As New SqlCommand(query)
        Using con As New SqlConnection(conString)
            Using sda As New SqlDataAdapter()
                cmd.Connection = con

                sda.SelectCommand = cmd
                Using dt As New DataTable()
                    sda.Fill(dt)
                    Return dt
                End Using
            End Using
        End Using
    End Function

auto balance or summing

I have a table for material consumption, same product with different cost and stock as per bellow

itcode  cost    stock
100       10    100
100       11    110
100       12    500
100       13    200

If I consume a quantity of 300 i need to get the result as follows

itcode  cost    stock   auto-deduct  balance_of_stock
100     10        100    100             0
100     11        110    110             0
100     12        500    90             410     

the quantity has to deduct automatically from beginning

SQL Server 2008 R2 installation nightmare

I'm not a SQL Server specialist, i'm only a software developer. But i've been using SQL Server since version 7.

I have apps that use SQL Server, so i often need to install this DB on my customers machines.

Until SQL Server 2005 i had no issues ; i just installed , attach my DB and everything worked fine.

Some customers want upgrade to 2008 version and that's when the nightmare started.

It seems SQL Server 2008 has a lot of new 'security' restrictions and the fact is everytime i try to install it, it NEVER finishes the installation without problems.

Some errors i get include :

• 'Access denied' at the end of installation


• 'Error 1814' when trying to start the service


• Windows authentication not connecting

If i run the SQL Sever 2005 installation, it works fine as always.

Due to all these problems, can anyone point me to some advices to how really get a fluid and efficient install of SQL Server 2008 R2, just like 2005 worked ?

Thanks in advance !

Left Outer Join (or) Union which is more efficient

I am working on a search functionality where in the database is designed as shown.

I have to return the top 20 rows where

• TableA.[Column 3] OR TableC.[Column B] OR TableB.[Column X] CONTAINS the @SearchText.

This is a massive database that has several million records in each one of its tables. A full-text catalog has been created on this database with several full-text indexes.

The Search Query is as follows ...

SELECT DISTINCT
  A.ID,
  A.Column1,
  A.Column2,
  A.Column3,
  L.Description
FROM TableA A
LEFT OUTER JOIN Lookup L ON A.Code = L.Code
WHERE CONTAINS(A.Column3, @SearchText)
UNION
SELECT DISTINCT
  A.ID,
  A.Column1,
  A.Column2,
  A.Column3,
  L.Description
FROM TableA A
LEFT OUTER JOIN Lookup L ON A.Code = L.Code
LEFT OUTER JOIN TableB B ON A.Id = B.FK_ID
WHERE CONTAINS(B.ColumnX, @SearchText)
UNION
SELECT DISTINCT
  A.ID,
  A.Column1,
  A.Column2,
  A.Column3,
  L.Description
FROM TableA A
LEFT OUTER JOIN Lookup L ON A.Code = L.Code
LEFT OUTER JOIN TableC C ON A.Id = C.FK_ID
WHERE CONTAINS(C.ColumnB, @SearchText)

Questions

1) Will this Query perform better - it is very concise.

SELECT DISTINCT
  A.ID,
  A.Column1,
  A.Column2,
  A.Column3,
  L.Description
FROM TableA A
  LEFT OUTER JOIN Lookup L ON A.Code = L.Code
  LEFT OUTER JOIN TableB B ON A.Id = B.FK_ID
  LEFT OUTER JOIN TableC C ON A.Id = C.FK_ID
WHERE CONTAINS((A.Column3, B.ColumnX, C.ColumnB), @SearchText)

2) Will creating a view MyView with the lookup table left outer joined with TableA improve performance as in ...

SELECT DISTINCT
  V.ID,
  V.Column1,
  V.Column2,
  V.Column3,
  V.Description
FROM MyView V
  LEFT OUTER JOIN TableB B ON V.Id = B.FK_ID
  LEFT OUTER JOIN TableC C ON V.Id = C.FK_ID
WHERE CONTAINS((V.Column3, B.ColumnX, C.ColumnB), @SearchText)

How to sum cells in a column per id in sql server

I am new to Sql server. the sum(datediff(x,y,z)) is giving the sum of both a1 and a2 whereas I want the sum separate for a1 and a2. I dont know how to group it per id. I know that sum will return the sum of both but I want to know how can I separate them.

select email,
    sum(datediff(day, x, y))
from abc
where users.id ='a1'
    or users.id='a2'
group by email, users.id

email|datex|datey|
a1   |1    |2
a1   |1    |3
a1   |2    |5
a2   |2    |7
a2   |3    |7
a2   |1    |8

So what I need

email|diff
a1   |4
a2   |6

How can I find exact number of minutes without rounding between two dates in sql server

I am new to Sql server, I tried datediff several times but failed. All I need is the exact or precise number of minutes including fraction between two dates. E.g if the gap between two datetime fields is 40 seconds than it gives me 0.67 minutes and not 1 minute. Please help.

Explain SQL Code Snippit

I copied this code from another thread here (T-SQL: Opposite to string concatenation - how to split string into multiple records) and it works great for what I needed it to do but if asked, I couldn't explain exactly how it does what it accomplishes...can someone explain what the Recursive CTE is doing step by step please?

 WITH Pieces(pn, start, stop) AS (
  SELECT 1, 1, CHARINDEX(@sep, @s)
  UNION ALL
  SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
  FROM Pieces
  WHERE stop > 0
)
SELECT pn,
  SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces

How do i put my whole querry in a stored Procedure?

Select 
AvHours.LineNumber,
(SProd.PoundsMade / (AvHours.AvailableHRS - SUM (ProdDtime.DownTimeHRS))) AS Throughput,
SUM (ProdDtime.DownTimeHRS) AS [Lost Time], 
(SUM(cast(ProdDtime.DownTimeHRS AS decimal(10,1))) * 100) / (cast(AvHours.AvailableHRS AS decimal(10,1))) AS [%DownTime],
SUM(SProd.PoundsMade) AS [Pounds Made],
(SProd.PoundsMade / (AvHours.AvailableHRS - SUM (ProdDtime.DownTimeHRS))) *  SUM (ProdDtime.DownTimeHRS) AS [Pounds Lost]

FROM rpt_Line_Shift_AvailableHrs AvHours inner join rpt_Line_Shift_Prod SProd on AvHours.LineNumber=SProd.LineNumber AND AvHours.Shiftnumber=SProd.Shiftnumber inner join rpt_Line_Shift_ProdDownTime ProdDtime on (AvHours.LineNumber=ProdDtime.LineNumber AND AvHours.Shiftnumber=ProdDtime.Shiftnumber) GROUP BY AvHours.LineNumber,SProd.PoundsMade,AvHours.AvailableHRS ORDER BY AvHours.LineNumber

How to get DATE from DATETIME column?

I want to get date from DateTime column.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
SelectCommand="SELECT [name], [book_id], [book_name], [taken_date], [last_date], [renewed_date], [status], [comment], [fine_amount] FROM [library] WHERE ([admn_no] = @admn_no)">
<SelectParameters>
   <asp:ControlParameter ControlID="TextBox1" Name="admn_no" PropertyName="Text" 
   Type="String" />
</SelectParameters>
</asp:SqlDataSource>

From the above these [taken_date],[last_date],[renewed_date] columns showing date and time

(eg: 4/14/2015 12:00:00 AM).

How do I display only date

(eg: 4/14/2015)?

How to split a single column into multiple columns in sql-server select statement?

I am new to sql-server. I have a single long column with names starting from a, b, c and d. I want to show them in separate columns of NameA, NameB, NameC and NameD. I tried union but it shows in one column. I tried case but I dont know how to use it. Please help.

Select users where users.name like 'a%'
union
Select users where users.name like 'b%'

How do I get the next available max number?

Did my title make sense? i'm sorry if it didn't.

From this column, how do I query so that I can get 5 as a result?

Number
-----
  1
  2
  3
  4
  6
  7

SQL Server Error: 10054

I am having problems connecting our server to its local database connection. (No problems connecting remotely)

See screenshot below:

I've tried adding SynAttackProtect value on registry but error is still the same.

Thanks in advance.

NUnit Rollback attribute seems to fail on SQL Server 2005

I'm using a nice little [Rollback] attribute on my NUnit database-level tests:

public class RollbackAttribute : Attribute, ITestAction 
{
   private TransactionScope _transaction;

   public ActionTargets Targets {
      get { return ActionTargets.Test; }
   }

   public void BeforeTest(TestDetails testDetails) {
      _transaction = new TransactionScope();
   }

   public void AfterTest(TestDetails testDetails) {
      _transaction.Dispose();
   }
}

So I can then decorate my db-based tests like this:

[Test]
[Rollback]
public void TestGetAllActiveItems()
{
    // Arrange
    SetupTestData();

    // Act
    var results = GetAllActiveItems(string.Empty, string.Empty);

    // Assert
    Assert.IsNotNull(results);
}

The sample data I create and store in the SetupTestData() method is used for the test, and then discarded at the end of the test.

This works like a charm on SQL Server 2012 and 2014 on my local dev machine - but for some reason, it appears to fail miserably on our build & test machine, which is still using SQL Server 2005 (soon to be upgraded).

Any ideas why? I just see that my two items that I insert into the database in my SetupTestData() method are inserted once for each test method, and NOT rolled back at the end of each method, for some weird reason.... I don't see any errors or other indicators in the build / testrun logs - it just doesn't work and doesn't do the rollback on my build/test server.

Any pointers? Thoughts? Points to check?

How to get last record of a table?

I have a table tbl_duplicate with 3 columns: 'bill_no' , 'name' , 'room_no'.

I want to get last inserted record of this table. I don't have any auto increment column or any unique column. then is it possible to fetch current inserted record using any SQL query?

how to use temporary table in function or use table type in sql server 2005

I have one stored procedure where I need to create temporary table/table type (I don't know exactly what to use). And I have one function which insert data according to matching case in the table (which is return type of function) of function. The scenario is like below.

stored_procedure
begin
   @temp_table
   my_function(@temp_table)
end


function my_function(@temp_table)
returns @result_table table
( value1 varchar(100),
  value2 varchar(100),
  ... )
begin
   case something   -- > insert into @result_table
   case something_1 -- > insert into @result_table
   case something_2 -- > insert into @result_table
end

Note: I am working on SQL_SERVER 2005

The data of temporary table is needed in where clause (cross join in where condition) in various cases of function

How to join an existing table that has starting date and closing date to the existing calendar table to calculate working days

How to join an existing table that has starting date and closing date to the existing calendar table to calculate working days .

My calendar table looks like this :-

dt  isweekday   isholiday   Y   FY  Q   M   D   DW  monthname   dayname W
1/1/2010 0:00   FALSE   FALSE   2010    2010    1   1   1   6   January Friday  1
1/2/2010 0:00   FALSE   FALSE   2010    2010    1   1   2   7   January Saturday    1
1/3/2010 0:00   TRUE    FALSE   2010    2010    1   1   3   1   January Sunday  2
1/4/2010 0:00   TRUE    FALSE   2010    2010    1   1   4   2   January Monday  2
1/5/2010 0:00   TRUE    FALSE   2010    2010    1   1   5   3   January Tuesday 2
1/6/2010 0:00   TRUE    FALSE   2010    2010    1   1   6   4   January Wednesday   2

and the other table containing start date and end date looks like the following :-

orderid orderdate   ordercloseddate actionby
40978   4/15/2010 12:47 4/18/2010 14:47 tjjohn

SQL Server Arithmetic overflow error converting expression to data type datetime

I want to customize my order by , if the Qty is negative then that row must be in fist then if the Qty is positive I will sort it by ExpDate

My Query is this .

Select WhsCode,ItemCode,LotNumber,ExpDate,Qty from rq_Test2 order by case when qty < 0 then Qty else ExpDate end

But I am getting an error of " Arithmetic overflow error converting expression to data type datetime. " .. Why ?

Thanks ..

Migrating SSRS Reports to another server

I am recreating the current ssrs environment for a dev env. I need to move the existing reporting services over to this environment. I need the reports that are deployed there to be deployed in the new dev environment. I installed SSRS in the dev environment and set it up but I obviously don't have any of the reports or data because it's a brand new instance.

This is in SQL Server 2005 with one instance. Can I backup and restore the prod database to the dev database and just point the Report Service to the restored database (under a different name).. and restore the key as well.

Can I do this without taking the prod database offline?

Basically I need to recreate / copy the prod DB to a dev DB in one database server / instance.

Convert a VARCHAR into XML that contains reserved characters

Basically I am trying to split a string based on delimiter. Below is partial code -

DECLARE @Str VARCHAR(MAX)
DECLARE @delimiter CHAR(3)
DECLARE @xmlPD xml

SET @delimiter = '~~~'
SET @Str = 'abc~~~I&IK blah blah'
SET @xmlPD = CAST(('<X>'+REPLACE(@Str, @delimiter, '</X><X>')+'</X>') AS XML)

The problem is as @Str contains & very last line is throwing below error.

Msg 9411, Level 16, State 1, Line 8
XML parsing: line 1, character 18, semicolon expected

How can I handle this scenario.

NOTE:

• 
  

  I will insert the splitted data into a table. So, I have to insert & as & not &.

  
  


• 
  

  @Str may contain & as well as &

  
  


• 
  

  We are using SQL-SERVER 2005

  
  

Join two SQL select statements and get both results in to single

I want to join these two SQL select statements and get both results as a single result.

--1st select statement
SELECT TA.DevID, TA.Task, TA.AssignDate,  TA.DevStart,TA.TaskType, TA.EstimateTime, TA.Status, TA.DevFinish, TAKPI.actAsignTime,  TAKPI.actTime, TAKPI.KPI
FROM TT_TaskAssign TA
INNER JOIN HR_EmployeeMaster1 EM
on TA.DevID=EM.EmpNo 
INNER JOIN dbo.TT_TaskAsignKPI TAKPI
ON TAKPI.AssignID = TA.AssignID 
WHERE TA.DevID='1435'

--2nd select statement
SELECT  TT_TaskAssign.DevID AS DevID  ,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName as Developer, SUM( CASE WHEN TT_TaskAssign.Status='Done' THEN 1 ELSE 0 END ) as DoneProjects , SUM( CASE WHEN TT_TaskAssign.Status='Developing' THEN 1 ELSE 0 END ) as DevelopingPrjects, SUM( CASE WHEN TT_TaskAssign.Status='Assign' THEN 1 ELSE 0 END ) as AssignedPending, SUM(CONVERT(int, TAKPI.KPINum)) AS KPINum 
FROM TT_TaskAssign inner join Project_Master on TT_TaskAssign.ProID=Project_Master.Project_Code Inner Join HR_EmployeeMaster1 on TT_TaskAssign.DevID=HR_EmployeeMaster1.EmpNo INNER JOIN dbo.TT_TaskAsignKPI TAKPI ON TAKPI.AssignID = TAKPI.AssignID
WHERE TT_TaskAssign.DevID='1435'
GROUP BY  TT_TaskAssign.DevID,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName
ORDER BY SUM(CONVERT(int, TAKPI.KPINum)) DESC

I did it in follwing way

SELECT T1.*, T2.*
FROM 
  (SELECT TA.DevID, TA.Task, TA.AssignDate,  TA.DevStart,TA.TaskType, TA.EstimateTime, TA.Status, TA.DevFinish, TAKPI.actAsignTime,  TAKPI.actTime, TAKPI.KPI
  FROM TT_TaskAssign TA
  INNER JOIN HR_EmployeeMaster1 EM
  on TA.DevID=EM.EmpNo 
  INNER JOIN dbo.TT_TaskAsignKPI TAKPI
  ON TAKPI.AssignID = TA.AssignID 
  WHERE TA.DevID='1435') T1

INNER JOIN
  (SELECT  TT_TaskAssign.DevID AS DevID  ,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName as Developer, SUM( CASE WHEN TT_TaskAssign.Status='Done' THEN 1 ELSE 0 END ) as DoneProjects , SUM( CASE WHEN TT_TaskAssign.Status='Developing' THEN 1 ELSE 0 END ) as DevelopingPrjects, SUM( CASE WHEN TT_TaskAssign.Status='Assign' THEN 1 ELSE 0 END ) as AssignedPending, SUM(CONVERT(int, TAKPI.KPINum)) AS KPINum 
   FROM TT_TaskAssign inner join Project_Master on TT_TaskAssign.ProID=Project_Master.Project_Code Inner Join HR_EmployeeMaster1 on TT_TaskAssign.DevID=HR_EmployeeMaster1.EmpNo INNER JOIN dbo.TT_TaskAsignKPI TAKPI ON TAKPI.AssignID = TAKPI.AssignID
  WHERE TT_TaskAssign.DevID='1435'
  GROUP BY  TT_TaskAssign.DevID,HR_EmployeeMaster1.EmpFirstName+' '+HR_EmployeeMaster1.EmpMiddleName+' '+HR_EmployeeMaster1.EmpLastName
  ORDER BY SUM(CONVERT(int, TAKPI.KPINum)) DESC) T2

ON T1.DevID=T2.DevID 

Now It's giving following error

    Msg 1033, Level 15, State 1, Line 17 
    The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Please somebody help me to fix this error

if any duplicate data in table then status show 1 of all data unique in table then status show o in sql server

I have small question about SQL Server - how to solve this issue?

Table: emp

id | name | sal
----------------
 1 | abc  | 100
 2 | ha   | 200
 1 | abc  | 100
 1 | abc  | 100
 1 | abc  | 100
 2 | ha   | 200
 2 | ha   | 200
 3 | hai  | 400

Based on this data I want give data duplicate or not in the table for that status i out show output.

I tried like this:

select case when count(*)>1 then 1 else 0 end as 'Status'from emp group by id,name,sal

I get this output:

status
1
1
0

I do not want get output like above way.

I want show output like below

Status
    1

when data comes unique in table that time status shows : 0 value only

1 means duplicate data and o means unique records in that table. if any records have duplicate finaly output show one(1) only .no need to display corresponding duplicate values status show 1 .If all records unique in that table I want display only 0(zero). no need to display corresponding unique value status please tell me how to get singe status values to achive this issue.

sql server dense_rank() on nvarchar column

From this question, is it possible to use dense_rank properly on an nvarchar column?

here's an sql fiddle where I created an nvarchar column, see the results yourself, and here's one where the column is int

I want to update multiple date field rows into one single row

I have a table as shown below. Here is a sample table with multiple records. In Jan, March, April and then in June there are 3 records.

PeriodID    Period_From Period_To   Member  FirstName   LastName    CodeID
1            1/16/2014  1/28/2014   151       Robert    Flow        TWIT
2            3/7/2014   3/20/2014   151       Robert    Flow        TWIT
3            4/3/2014   4/10/2014   151       Robert    Flow        TWIT
4            6/2/2014   6/4/2014    151       Robert    Flow        TWIT
5            6/6/2014   6/8/2014    151       Robert    Flow        TWIT
6            6/13/2014  6/19/2014   151       Robert    Flow        TWIT

So for Jan, March and April Date-Periods we treat as separate Periods/records. and for June I need only 2 records if the 'Period_From' is > 2 days of Previous record (i.e from 'Period_To') then we consider as separate record else take the Period_From from 1st record and Period_To from 2nd record and update in 1 single row.

I want solutions as below:

PeriodID    Period_From Period_To   Member  FirstName   LastName    CodeID
1            1/16/2014  1/28/2014   151       Robert    Flow        TWIT
2            3/7/2014   3/20/2014   151       Robert    Flow        TWIT
3            4/3/2014   4/10/2014   151       Robert    Flow        TWIT
4            6/2/2014   6/8/2014    151       Robert    Flow        TWIT
6            6/13/2014  6/19/2014   151       Robert    Flow        TWIT