I have written the following lines myConnection = New MySqlConnection("server=" + dbServer + "; user id=" + dbUserID + "; password=" + dbPassword + "; database=" + dbName + "; pooling=false;")
strSQL = "SELECT * FROM user where type=1;"
user table has name, tel, addr, id, type fields
I would like to know how to use a string array to store the name in the result of strSQL?
Thank you
Hi All,
I receive the input file with some 100 columns and some 20k+ rows and I want to check the incoming input row is existed in the database or not based on 2 key columns. If the row is existed then I need to check all the columns (nearly 100 columns) values in input and the database are equal or not. If both are equal I need to treat them seperately if not there is a seperate logic. How Can I do that check for each row and for each column?
Basically the algorithm is like this, if the input file row is not existed in the database then treat that as new row else if the input row is existed in the database then check all the columns are equal or not. If all the columns are equal then treat that as existing row and do nothing else if some columns are not equal then treat this row seperately.
I found some thing to achieve the above thing.
1. Take the input row and check in the database.
2. If the row is not found in the database then treat it as new row.
3. If row is found in the database then
a) Take the source row and prepare a concatenated string for all the columns
b) Take the database row and prepare a concatenated string for all the columns
c) Find out the hash code for the 2 strings and then compare hash codes for equal.
The disadvantage of this is running a loop 2*m*n times where m is the number of rows and n is the number of columns. It should be done 2 times for input file row and database row.
Can anybody suggest a good method to do this?
What does the function "GetHashCode" for InputBuffer in method "Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)" will do?
Will it generates hash code based on all the columns values?
Pls clarify.
Regards
Venkat.
Hi all,
I put "Northwind" Database in the Database Explorer of my VB 2005 Express and I have created the following stored procedure in the Database Exploror:
--User-defined stored procedure 'InsertCustomer'--
ALTER PROCEDURE dbo.InsertCustomer
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
INSERT INTO Customers
(
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
)
VALUES
(
@CustomerID,
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@Region,
@PostalCode,
@Country,
@Phone,
@Fax
)
=================================================
In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code:
--Form_Kimmel.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=northwind;Data Source=NAB-WK-EN12345"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@CustomerID", "PAULK")
command.Parameters.Add("@CompanyName", "Pauly's Bar")
command.Parameters.Add("@ContactName", "Paul Kimmel")
command.Parameters.Add("@ContactTitle", "The Fat Man")
command.Parameters.Add("@Address", "31025 La Jolla")
command.Parameters.Add("@City", "Inglewoog")
command.Parameters.Add("@Region", "CA")
command.Parameters.Add("@Counrty", "USA")
command.Parameters.Add("@PostalCode", "90425")
command.Parameters.Add("@Phone", "(415) 555-1234")
command.Parameters.Add("@Fax", "(415 555-1235")
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
Thanks in advance,
Scott Chang
I was able to connect to the SQL Database Pension with table clients with table values: ID, State, Name.'Create(connection)Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)'open connectionconn.Open()However, I'm not sure how to insert a new row with an incremental ID number and a new State and Name.Sorry, I'm really new with VWD.
View 1 Replies View RelatedIm writing a sp but have a hard time debugging...How can I check the values used in my stored procedure, the resultset after executing the sp AND see what actually gets sent to the server (query+values)?
View 2 Replies View RelatedI've done this before, but I think I'm having a brain stall. If I have a table with the following columns/data, how do I query it in such a way that returns only the row for each resident with the maximum value for Daterecd, i.e. the most recent payment? Jeez, this is so basic. I must be having a nervous breakdown.
PymtIDResidentsIDLastFirstDateRecdDaysAmount
11000JonesBridget7/15/20047$777.00
21000JonesBridget8/15/200431$6,441.06
41000JonesBridget9/15/200431$6,441.06
51001LongstockingPippi9/15/200639$7,851.00
61001LongstockingPippi10/15/200630$6,441.00
71002OrganaLeia6/15/200630$6,441.00
91002OrganaLeia7/15/200630$6,441.00
101002OrganaLeia8/15/200630$6,441.00
Hi,
I have a problem to get the max values. I have table like this...
STATUS CUSTOMER NUMBER OTHER
0 000001 1 DATA1
0 000001 2 DATA2
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 1 DATA1
0 000003 2 DATA2
I need to get this
STATUS CUSTOMER NUMBER OTHER
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 2 DATA2
Please advice
Thanks
I have faced a situation that when i try to update a page. Some values can be updated while some cannot. I try to print the executed SQL query and get the following1 "UPDATE orders SET
2 cust_id=15,po_code='PO20060610',
3 po_amt=10000.0000,
4 add_charges=0,
5 commission='eeeeeee',
6 lab_charges=0,
7 fty_dis=0,
8 pay_trm='adasds',
9 cust_dis=0,
10 trade_trm_desc='',
11 curr_rate=1,
12 ship_expense=0,
13 shipmark='eng ship mard new2',
14 sidemark='Eng Side Mark new333'
15 ,inner_box='Eng Inner Box new333',
16 confirmation='rend confirmation2',
17 contract='end contact23',
18 internal_remark='testing testing 26/6/2007 333',
19 rec_curr_rate=0,rec_amt=0,shipmark_attach='',
20 sidemark_attach='',inner_box_attach='',
21 ord_type=1,status=2,ord_confirm_code='',
22 commission_type=1,sidemark_lang='English',
23 curr_code='HKD',unit_code='PCS',
24 trade_trm='FOB Hong Kong',rec_curr='USD',
25 ord_date='2006/06/10', po_date='01/01/2007',exp_delivery_date='01/01/2007',
26 act_delivery_date='01/01/2007', pay_start_date='10/10/06',pay_end_date='10/10/06',upd_time='2007/03/15 15:41:14' WHERE ord_id=292;Set @ord_id=292;"
The fields sidemark, inner_box, internal_remark cannot update, while others can.
I think it's really strange.... since i have no idea why some can be updated while some others and the SQL seems to me is correct.
Please give me some advices on solving this.
Thank you.
Hi, I have the following query.
sql = "select firstname AS Expr1, lastname AS Expr2, status AS Expr3 from person order by lastname"
Status is either 0,1,2 or 3
How can I use the query to create "a temp Alias" for the query, so that there is a "temp Alias" AS Expr4, AS Expr5, AS Expr6 and AS Expr7.
So if status = 0 then Expr4 = "true" else false
So if status = 1 then Expr5 = "true" else false
So if status = 2 then Expr6 = "true" else false
So if status = 3 then Expr7 = "true" else false
So when the reader reads Expr4 its either true or false.
Is this possible i a query?
Hello.
I need a query that will RETRIEVE a value from a database if it is present, but if the data isn't present, then the data will be INSERTed into the table.
Either way, I need the row returned at the end of the query.
I can do SELECT queries, but I don't have a clue as to how to proceed with branching statements.
For example:
User runs a query for "Canada".
Canada exists in the database, so the database returns Canada along with its ID.
Next user runs a query for "Chile".
Chile isn't in the database so a record is created and the ID (an IDENTITY field) is returned.
Does anyone know how I may accomplish this?
I have written sql query
select INVOICE.InvoiceTypeCode, INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,
INVOICETYPEMASTER.InvoiceTypeName, INVOICEITEMS.ItemQuantity as SumQuantity,
INVOICE.BasicValue ,INVOICE.BasicValue * INVOICE.ExchangeRate +
[Code].....
I am getting different amount 984000.0000 and quantity 9.
hello friends!!
i am getting values from front end application as 1,2
i am using it as '''1'',''2'''
i am writing stored procedure
create proc [dbo].[test_1]
@id varchar(40)
as
begin
declare @str as varchar(500)
select * from table_1 where convert(varchar,uid )in (select @id)
end
and another is
create proc [dbo].[test_1]
@id varchar(40)
as
begin
declare @str as varchar(500)
set @str = 'select * from table_1 where convert(varchar,uid )in ('+@id+')'
exec(@str)
end
my table structure is name : table_1
columns datatype
uid int
uname varchar(10)
why i am getting null values for first stored procedure and if i am using second one i am getting values in query analyser but not in front end i am using ms visual studio 2005 and getting @return_value as 0 but not actual data
is there any link where i find code source for getting data through stored procedure using exec(@querystr)
T.I.A
Hi every body.
Can u tell me how to get the order values of the SQL query
Example.
My sqlstring ="Select * from tbl_Products"
And it returns 6 rows
And I want to get order values like this 1,2,3,4,5,6
I am a beginner.
Thanks a lots
HiI have the following tables and stored procedure. I need to pass a value tothe stored procedure and have it use the value in a query. After runningthat query it will return an ID which is then used in an insert statement.At present the values in @MovieId int, @UserID int are left empty (see myoriginal code at the bottom of posting). I think this is due to an issuewith when the sql is executed (?).I get the feeling I should have something like this instead, but not sure:ALTER proc inserttransactions@MovieName nvarchar(50) = 'team',@uName nvarchar(50) = 'frank',@FrameNumber int = 0asDECLARE @MovieId int, @UserID int-- Find MovieID from MovieName@MovieId = exec MovieName2Id @MovieName-- Find UserID from UserEmail@UserEmail = exec Email2UserId @uName-- Insert DataINSERT INTO Transactions(MovieId, UserId, FrameNumber)VALUES (@MovieId, @UserID, @FrameNumber)Thanks in advance.========MY CODE=============Tables:CREATE TABLE [dbo].[movies] ([movieID] [int] IDENTITY (1, 1) NOT NULL ,[movieName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[movieDateAdded] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[transactions] ([userID] [int] NULL ,[movieID] [int] NULL ,[FrameNumber] [int] NOT NULL ,[transDate] [datetime] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[users] ([userID] [int] IDENTITY (1, 1) NOT NULL ,[userEmail] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,[userDateRegistered] [datetime] NULL) ON [PRIMARY]GOStored Procedure:ALTER proc inserttransactions@MovieName nvarchar(50) = 'team',@uName nvarchar(50) = 'frank',@FrameNumber int = 0asDECLARE @MovieId int, @UserID int-- Find MovieID from MovieNameSELECT @MovieId = MovieIdFROM MoviesWHERE MovieName = @MovieName-- Find UserID from UserEmailSELECT @UserID = UserIDFROM UsersWHERE UserEmail = @uName-- Insert DataINSERT INTO Transactions(MovieId, UserId, FrameNumber)VALUES (@MovieId, @UserID, @FrameNumber)GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 2 Replies View RelatedHi, I have been asked to write some code that can check a large table for duplicate values in a non pk column. The table may have up to 1000000 rows. The PK column is an auto increment field. For performance reasons the column in question could not be set to unique values only for inserts, an algorithm is used to create unique no's before the insert but what I am doing is double checking that their have been no duplicates created accidently. If their are duplicates I need to know what rows they occurred on.
Thanks
I have table that contains below data
Date ID
Message
2015-05-29 7:00:00 AOOze abc
2015-05-29 7:05:00 AOOze start
2015-05-29 7:10:00 AOOze pqy
2015-05-29 7:15:00 AOOze stop
2015-05-29 7:20:00 AOOze lmn
[code]....
and so on following the series for every set of different ID with 5 entries.I need to Find Maximum interval time for each ID and for condition in given message (between Start and Stop)For example, in above table
-For ID AOOze, in message "start" is logged at 7:05 and stop and 7:15, so interval is 10 mins
-For ID LaOze, in message "start" is logged at 7:30 and stop and 7:45, so interval is 15 mins
I am looking for a sql query that will return in below format
ID MAX interval
AOOze 15
LaOze 10
Hello I have a newbie question. If I have a table of the form:Table1{id, name} with the valuesid: 9 , name: test1,id: 7 , name: test2,id: 3 , name: test3,id: 15 , name: test4, id: 5 , name: test5,id: 13 , name: test6,.........If I have a list generated from user selection ( LIST{1, 7, 8, 15} ,) will I in a way be able to use this list in a query of the form, thus only having to make one query to the database: SELECT id, nameFROM Table1WHERE Table1.id in LIST Or is the solution to make multiple queries to the database, one for each member of the list, of the form:SELECT id, nameFROM Table1WHERE ID = @IDThanks in advance /dresen
View 4 Replies View RelatedI want the following query to return a row even when table 'X' is empty. How would I do this?
SELECT TOP 1 @Var1, @Var2, @Var3 from X
The parameters @Var1, @Var2 and @Var3 are passed to the stored procedure in which the above query is included.
When table is empty, it reurn nothing. It only return a row when table is not empty.
I have a query that varies depending on control values. For example, what comes after the SELECT, FROM, WHERE, or ORDER vary with what table they need to look in, how they want to look it up, what value they are looking for, and what order they want it displayed in. There are too many possibilities to write seperate queries for. The data found goes to a dataset and gets bound to a gridview. This is done inside a vb sub.
I tried to make a similar query in a selectcommand inside sqldatasource tags with if-then-elses in <% %>s but got an error saying something like "constructs were not allowed inside tags."
How do you make a varying query as a selectcommand inside datasource tags?
I am trying to have a query with the option for items to be null. (So users don't need to fill in the other fields if they choose not too) SELECT Tickets.TicketID, Tickets.UserID, Tickets.SystemID, Tickets.Title, Tickets.Description, Tickets.Software, Tickets.Date, Systems.OS, OS.OS AS OstitleFROM Tickets INNER JOIN Systems ON Tickets.SystemID = Systems.SystemID INNER JOIN OS ON Systems.OS = OS.osIDWHERE (Tickets.Title LIKE '%' + @title + '%') AND (Tickets.Software LIKE '%' + @software + '%') AND (Tickets.Description LIKE '%' + @descrip + '%') AND (Systems.OS = @osid) OR (@osid IS NULL)This works when i give the LIKE values % as a parameter. So they can choose to search by title + software but not description or description and nothing else etc etc etc. The problem is, the osid. If I give it a value it works but if i try to do null, *. or % it always displays every item in the databse ignoring any of the previous like statements. Anyone have an idea?
View 3 Replies View RelatedI am new to both ASP.net and this forum. I have seen some posts close to this, but none address this problem.
I have a SQL Server database on JOHN1 called 'siu_log' with a table called 'siu_log'. It has two fields: Scenarios char[20] and Machines char[20].
I have been adapting code from Build Your Own ASP.NET Website in C# & VB.NET by Zac Ruvalcaba to learn the language. Much of what you will see is his work adapted for my use.
First, the html:
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="SIU_Assign.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>SIU Interface Scenario Assignments</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
</HEAD>
<body>
<form id="Form1" method="post" runat="server">
<asp:datagrid id="scenariosDataGrid" runat="server" CellPadding="4" AutoGenerateColumns="False"
OnUpdateCommand="dg_Update" OnCancelCommand="dg_Cancel" OnEditCommand="dg_Edit" DataKeyField="Scenarios">
<ItemStyle BackColor="#00DDDD" ForeColor="#000000" />
<HeaderStyle BackColor="#003366" ForeColor="#FFFFFF" />
<Columns>
<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" />
<asp:BoundColumn DataField="Scenarios" HeaderText="Scenario" ReadOnly="True" />
<asp:TemplateColumn>
<HeaderTemplate>
Machine
</HeaderTemplate>
<ItemTemplate>
<%# Container.DataItem("Machines") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtMachine" Runat=server Text='<%# Container.DataItem("Machines") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>
<asp:Label id="resultLabel" runat="server"></asp:Label></form>
</body>
</HTML>
Then the CodeBehind:
Sub dg_Update(ByVal s As Object, ByVal e As DataGridCommandEventArgs)
Dim strMachineName, strScenarioName As String
Dim intResult As Integer
strScenarioName = Trim(scenariosDataGrid.DataKeys(e.Item.ItemIndex))
strMachineName = CType(e.Item.FindControl("txtMachine"), TextBox).Text
cmd = New SqlCommand("UPDATE siu_log SET Machines=@Machine " & _
"WHERE Scenarios=@Scenario", conn)
cmd.Parameters.Add("@Machine", strMachineName)
cmd.Parameters.Add("@Scenario", strScenarioName)
conn.Open()
intResult = cmd.ExecuteNonQuery()
resultLabel.Text = "The result was " & intResult & "."
conn.Close()
scenariosDataGrid.EditItemIndex = -1
BindData()
End Sub
The problem is the strMachineName variable always contains the previous contents of the text box -- not the new one. This makes the UPDATE query just push the old data back into the table.
Any suggestions?
Thanks!
John
Hello.
I have (2) related questions.
#1: I am using a paramterized query, but am unable to make it work if one of the values happens to be null.
if (Request.Form["txtLink1"] != "")
{
mySqlCmd.Parameters.Add(new SqlParameter("@link1",
SqlDbType.VarChar));
mySqlCmd.Parameters["@link1"].Value =
Request.Form["txtLink1"];
}
else
{
mySqlCmd.Parameters.Add(new SqlParameter("@link1",
SqlDbType.VarChar));
mySqlCmd.Parameters["@link1"].Value = null;
}
If txtLink1 happens to be empty, I want @link1 to enter null. The
column in the Sql Server allows for nulls, but I get an error message
that says no value was supplied. In short, how do I supply a null value
using a parameterized query?
#2: For debugging purposes, how can I view what my SQL string looks
like (with all the values entered) before it gets submitted to the
database? When I view the string, it still contains the placeholder
values (@link1) instead of the actual values.
Thanks in advance!
-Brenden
Using SQL Server 7 w/SP4.
I need to insert into another table the results of a query. Thought about using a temp table for the first query results, then querying those for the insert, but can't figure out how to pass the search results as a value.
Example:
SELECT ID, NAME FROM tbl2 WHERE ID BETWEEN 1 AND 50
IF @@ROWCOUNT > 0 BEGIN
INSERT INTO tblAudit
(ID, NAME)
VALUES
(@ID, @NAME)
END
Took a look at using SELECT INTO and INSERT INTO, but counldn't get those to work, either.
Thanks for any help.
Hi!
Can anybody help me in solving this problem
SCRIPT
declare @var1 varchar(10)
set @var1 = '1,4,9,10'
select @var1
select TGNO, TGNAME from carriers where TGNO in (@var1)
RESULT
----------
1,4,9,10
(1 row(s) affected)
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '1,4,9,10' to a column of data type smallint.
I want to pass a list of values to a column of DATA Type smallint.
Thanks in advance.
What is the way to pass the values throug varibale as shown below?
SCRIPT
declare @var1 varchar(10)
set @var1 = '1,4,9,10'
select @var1
select TGNO, TGNAME from carriers where TGNO in (@var1)
RESULT
----------
1,4,9,10
(1 row(s) affected)
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value '1,4,9,10' to a column of data type smallint.
I want to pass a list of values to a column of DATA Type smallint.
Hello everyone. This is my first post here, so be gentle =)
I need to construct a query, that would return roughly 6000 rows of data.
There are some conditions, or joins, that I can't figure out. Maybe you could help me?
This is the first one.
Invoice.ID-DocParty.DocumentID -> DocParty.OtherID-Party.ID -> Party.IDNumber
This can be achieved with inner join, no problem. Pretty simple.
However, there's a catch =)
DocParty.Role can have four different values in the 'where' clause. Is there a
way to fetch all of these four values without returning four duplicates with
only one field differing?
There are multiple fields in the query that are to be fetched in similar ways. Therefore,
using a IN('value1','value2','value3','value4') would increase the number of selected rows
a lot.
In addition, there is another type of condition that needs to be fullfilled.
Invoice.Type1Account-Account.ID -> Account.Number
Invoice.Type2Account-Account.ID -> Account.Number
Basically, there two fields in the 'main' table that are joined to the same field in another table
with different conditions. Can this be fetched with the same row as all the other data without duplicates?
Should I use a view somehow? How can I construct a view with these complex conditions if I can't
construct an SQL query, that would return no duplicates (pseudo-du
I have two columns, where I have the start and stop numbers (and each of them ordered asc). I would like to get a query that will tell me the missing range.
For example, after the first row, the second row is now 2617 and 3775. However, I would like to know the missing values, i.e. 2297 for start and 2616 for stop and so on as we go down the series. Thanks in advance to any help provided!
StartStop
---------
20452296
26173775
568936948
3727084237
84409178779
179013179995
180278259121
259292306409
307617366511
I want to know if MS SQL has ability to INSERT multiple values with one statement. For example MySQL is able to do it like so
INSERT INTO myTable (col1,col2,col3)
VALUES
('1','1','1'),
('2','2','2'),
('3','3','3'),
('4','4','4');
and this will insert four rows.
Does MS SQL Have anything similar or do I have to repeat the INSERT statement if I need to generate a dynamic insert?
Thanks,
Lito
I am getting the following results from my query that contains a subquery, but I don't understand why the values in the [Total Volume M_Active_Previous] are being repeated with the same value. I should be getting different values returned for each row like in the [Total Volume M_Active] column.
Why the values are all the same and how I can fix this?
DayNoDayTotal Volume M_ActiveTotal Volume M_Active_Previous
6 Friday11161 72491
7 Saturday5687 72491
2 Monday14354 72491
1 Sunday3966 72491
4 Wednesday12340 72491
3 Tuesday12018 72491
5 Thursday11833 72491
Here is the SQL I'm using.
Code:
DECLARE @StartDate datetime, @EndDate datetime, @Prev_StartDate datetime, @Prev_EndDate datetime
SET @StartDate = '2011-03-13 00:00:00.000'
SET @EndDate = '2011-03-19 23:59:59.999'
SET @Prev_StartDate = '2011-03-06 00:00:00.000'
SET @Prev_EndDate = '2011-03-12 23:59:59.999'
[Code] ....
I am currently reading through Itzik Ben-Gan's "Microsoft SQL Server 2012 High-Performance T-SQL using Windows Functions." In attempt to test the SUM OVER() function in SQL 2008 because that's what I've got. I do not currently have sample data (trying to generate it has become a major PITA), but I have some pseudocode.
My current code (actual production code) pulls a bunch of ITD (inception to date) contracts then calculates a certain dollar amount based on monthly changes. Not all contracts have values during a given month, so here's what I cobbled together a few months ago. (Per our finance team, these numbers ARE accurate).
WITH MonthlyVals AS
(SELECT ContractID, SUM(Col1 - (Col2 + Col3 + Col4 + Col5)) AS MyTotal
FROM MyTable
WHERE MyDate >= @ThisMonthStartDate AND MyDate <= @ThisMonthEndDate
AND StatementType IN (8,4,2)
[code]....
To test the totals, I also added a COMPUTE SUM(MyTotal) to the end of each query. (Yes, I know COMPUTE is deprecated. Just wanted a quick check.). The difference between the two bits of code was over 68k, with the SUM OVER() code coming up with a total higher than the CTE code. I know CTE code is correct for a fact. It went through extensive testing before getting put in Production. Is it the way I joined the table for the SUM OVER()? Or is it the use of PARITION BY?
How would be a query that read detailed sales from several dates and accumulated them in a table that has months as fields
MSSQL 2005
Table : DETAIL
Customer_Date___________Value
1________01-Jan-2008____1,234.00
2________01-Feb-2008____3,456.00
3________01-Mar-2008______500.00
4________01-Apr-2008____1,567.00
1________01-Apr-2008____2,123.00
Table : TOTAL
Year__Customer_Jan______Feb______Mar_____Apr ...
2008__1________1,234.00_0________0_______2,123.00
2008__2________0________3,456.00_0_______0
2008__3________0________0________500.00__0
2008__4________0________0________0_______1,567.00
Thanks for your help
JG
I have a grid like this and when I change Designation value of Name X from PA to PAT, this change should reflect to Sathish Designation too. Changing similar values should reflect all over the grid. Like update database on change of value of similar kind.
By far I use this query to update the grid. So a where clause should be used now to update the grid to fit this scenario.
("UPDATE AppInvent_Test SET Name = @Name, Designation = @Designation, City = @City WHERE EmpID = @EmpID");
EmpID Name Designation City
21 X PA Chn
2 Sathish PA Chn
3 Shiva A Cbe
17 Venkat M Hyd
22 Y SM Cbe
18 Vignesh SA Hyd
Table:
CREATE TABLE [dbo].[AppInvent_Test](
[EmpID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Designation] [varchar](100) NULL,
[Code] ....
I have a grid with checkbox, where users can select multiple rows and edit at the same time and save it to the DB. Now I have used a Footer Template with textbox in the gridview. So if I want to put similar data's for some particular rows at the same time in the grid, I select the multiple rows and try to put values in the footer template textbox and when I click on save, it saves successfully.
UPDATE QUERY:
"UPDATE [Test] SET [Name]='" + Name + "',[Designation]= '" + Designation + "', [City]= '" + City + "' WHERE EmpID='" + EmpID + "'";
Now here is the challenge, but even when I enter null values in the footer template textbox it has to save with the old values of the rows and not null values. I tried it and couldn't make it happen. So anything like putting the case for each column and mentioning like if null accept the old value and not null accept new value.