Increase one month

Hi,
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
MichaelTry with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
Lookup the DATEADD function in Books Online.
You will find that you can get more detailed and useful answers if you
supply more information. Read my signature.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> > Hi,
> > I have a date value of 4/1/2005, I wanted to increase just one month
> > to 5/1/2005. I have millions of records like that to increase just one
> > month. Is there a function I can use?
> > Thanks a lot!!
> > Michael
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
thanks a lot all!!!

Increase one month

Hi,
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
Michael
Try with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegr oups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegr oups.com...
>
>
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
thanks a lot all!!!

Increase one month

Hi,
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
Michael"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
Lookup the DATEADD function in Books Online.
You will find that you can get more detailed and useful answers if you
supply more information. Read my signature.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Try with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
>
>
>
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:http://msdn2.microsoft.com/library/ms130214(en-US,
SQL.90).aspx
> --
thanks a lot all!!!

Increase nvarchar field value like a num

Hi everybody,
i want write a stored procedure. This is increase NVARCHAR (7) field like a
number.
Example :
A00001
A00002
:
:
A99999
B00001
:
:
Z99999
AA00001
:
ZZ99999
:
Error
How can i do ? can i do this with t-sql?
thanksUse an insert trigger
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hi everybody,
> i want write a stored procedure. This is increase NVARCHAR (7) field like
> a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
> thanks
>|||Stored procedure must return a value
"Martin" <x@.y.z>, haber iletisinde unlar
yazd:ORRgvV18FHA.476@.TK2MSFTNGP15.phx.gbl...
> Use an insert trigger
> "SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
> news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
>|||The fastest way to to do this would be to use a lookup table; set a
bigint value to be the order-determinant (eg, 1, 2, 3,) and use the
other values as a lookup:
CREATE TABLE (ID bigint, Value NVARCHAR(7))
INSERT INTO TABLE (ID, Value)
--write a routine to populate this
VALUES (1, 'A00001')
Your stored procedure would then return the ID value bases on the
values you supply, increment the ID by one, and return the next value
in sequence. Kind of like a calendar table or a table of numbers.
HTH,
Stu|||SharkSpeed (sharkspeedtr@.yahoo.com) writes:
> i want write a stored procedure. This is increase NVARCHAR (7) field
> like a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
DECLARE @.letters varchar(2)
@.digits varchar(5)
SELECT @.digits = right(@.input, 5),
@.letters = substring(@.input, 1,
CASE len(@.input) WHEN 6 THEN 1 ELSE 2 END)
IF @.digits <> '99999'
BEGIN
SELECT @.digits = substring(convert(varchar(
convert(int, @.digits) + 100001)), 2, 5)
END
ELSE IF len(@.letters) = 1 and @.letters <> 'Z'
SELECT @.letters = char(ascii(@.letters) + 1))
ELSE IF @.letters = 'Z'
SELECT @.letters = 'AA'
ELSE IF @.letters NOT LIKE '_Z'
SELECT @.letters = substring(@.letters, 1, 1) +
char(ascii(substring(@.letters, 2, 1) + 1))
ELSE IF @.letters <> 'ZZ'
SELECT @.letters = char(ascii(substring(@.letters, 1, 1)) + 1) + 'A'
ELSE
RAISERROR ('Cannot compute a successor key to ZZ99999', 16, 1)
I did not test this, nor did I try to compile. You should be able to
make something out of it anyway.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In line with what Erland posted I had started on something.
I have now also tested so you could implement this as it is.. no
warranties though.
first create this table:
CREATE TABLE nextIDTable (preChar varchar(2), postInt int)
INSERT INTO nextIDTable (preChar, postInt) values ('A', 1)
Then once you have the table and inserted the values above you can
implement the stored procedure, calling this will increment the varchar
"number" as you described you wanted:
CREATE PROC getNextID
@.nextID nvarchar(7) OUTPUT
AS
DECLARE @.MyCounter INT, @.LeadingZeros char(4), @.preChar varchar(2),
@.postInt int
-- Initialize the variable.
SET @.MyCounter = 0
SET @.postInt = (SELECT postInt FROM nextIDTable)
SET @.preChar = (SELECT RTRIM(preChar) FROM nextIDTable)
IF(@.postInt < 10) SET @.LeadingZeros = '0000'
IF(@.postInt >= 10 AND @.postInt < 100) SET @.LeadingZeros = '000'
IF(@.postInt >= 100 AND @.postInt < 1000) SET @.LeadingZeros = '00'
IF(@.postInt >= 1000 AND @.postInt < 90001) SET @.LeadingZeros = '0'
WHILE (@.MyCounter <= 51)
BEGIN
-- the loop is exited when @.MyCounter reaches -1
-- as all from ZZ to A have been checked
IF @.MyCounter = -1 return
-- for A through to Z
IF(@.MyCounter <= 25)
BEGIN
IF(@.postInt = 99999 and @.preChar = 'Z')
BEGIN
SET @.nextID = 'AA00001'
UPDATE nextIDTable SET preChar = 'AA', postInt = 1
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'Z')
BEGIN
IF(@.preChar = (CHAR(((@.MyCounter) + ASCII('A')))))
BEGIN
SET @.nextID = (CHAR(((@.MyCounter + 1) + ASCII('A')))) + '00001'
UPDATE nextIDTable SET preChar = CHAR(((@.MyCounter + 1) +
ASCII('A'))), postInt = 1
BREAK
END
END
ELSE
BEGIN
SET @.nextID = (@.preChar + RTRIM(@.LeadingZeros) + (CONVERT( char,
@.postInt)))
UPDATE nextIDTable SET postInt = postInt + 1
BREAK
END
END
-- for AA through to ZZ
IF(@.MyCounter > 25)
BEGIN
IF(@.postInt = 99999 AND @.preChar = 'ZZ')
BEGIN
-- reached the max value
RAISERROR('reached max val', 16, 1)
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
-- next char sequence + 00001
SET @.nextID = CHAR(((@.MyCounter - 26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + '00001'
UPDATE nextIDTable SET preChar = CHAR((@.MyCounter-26 + ASCII('A')))
+ CHAR((@.MyCounter-26 + ASCII('A'))), postInt = 1
BREAK
END
IF(@.postInt < 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
SET @.nextID = CHAR(((@.MyCounter-26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + RTRIM(@.LeadingZeros) + CONVERT(
char, @.postInt)
UPDATE nextIDTable SET postInt = @.postInt + 1
BREAK
END
END
SET @.MyCounter = @.MyCounter + 1
END
GO
good luck with it..
Gerard|||actually I just found there is a wee bug in the part after
IF(@.MyCounter > 25)
if your value is AA99999 it will jump to GG00001 but I think there's
enough here to make this work
Gerard

Increase Indentity value

How do I increase the current Indentity value for an existing Indentity column?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want it to return: 99999
dbcc checkident
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>
|||Ken,
DBCC CHECKIDENT (Quote, RESEED, 99999)
GO
Cheers,
Paul
|||http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>
|||Excellent, I have bookmarked this.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Aaron [SQL Server MVP] wrote:
> http://www.aspfaq.com/5003
>

Increase Indentity value

How do I increase the current Indentity value for an existing Indentity colu
mn?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want it to return: 99999dbcc checkident
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>|||Ken,
DBCC CHECKIDENT (Quote, RESEED, 99999)
GO
Cheers,
Paul|||http://www.aspfaq.com/5003
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:FAF19510-1D09-4D1C-BEA9-6934A3C8C473@.microsoft.com...
> How do I increase the current Indentity value for an existing Indentity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want it to return: 99999
>|||Excellent, I have bookmarked this.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Aaron [SQL Server MVP] wrote:
> http://www.aspfaq.com/5003
>sql

Increase Identity value

How do I increase the current Identity value of an existing Identity column?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want to increase it to, Returns: 99999
Thanks,
http://www.aspfaq.com/5007
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:C5A7066B-D4E3-48C3-B1E2-54B7E8DC4A14@.microsoft.com...
> How do I increase the current Identity value of an existing Identity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want to increase it to, Returns: 99999
> Thanks,

Increase Identity value

How do I increase the current Identity value of an existing Identity column?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want to increase it to, Returns: 99999
Thanks,
http://www.aspfaq.com/2237
For more information, please see DBCC CHECKIDENT in Books Online.
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:4BE94FEA-4CCF-4EA1-AE9E-F5A5B8057EAC@.microsoft.com...
> How do I increase the current Identity value of an existing Identity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want to increase it to, Returns: 99999
> Thanks,
>

Increase Identity value

How do I increase the current Identity value of an existing Identity column?
SELECT IDENT_CURRENT('Quote')
Returns: 16332
I want to increase it to, Returns: 99999
Thanks,http://www.aspfaq.com/2237
For more information, please see DBCC CHECKIDENT in Books Online.
http://www.aspfaq.com/
(Reverse address to reply.)
"KenL" <KenL@.discussions.microsoft.com> wrote in message
news:4BE94FEA-4CCF-4EA1-AE9E-F5A5B8057EAC@.microsoft.com...
> How do I increase the current Identity value of an existing Identity
column?
> SELECT IDENT_CURRENT('Quote')
> Returns: 16332
> I want to increase it to, Returns: 99999
> Thanks,
>

Incorrect value returned from Stored Procedure

I have an asp.net 1.1 website that uses sql server 2000 and vb.

I have a bit of a dilema, when I run a stored procedure in a webpage it returns the wrong value, but if I run it

in the query analyzer the correct value is returned.

 Dim orderHistory As nlb.OrdersDB = New nlb.OrdersDB ' Obtain Order ID from QueryString Dim OrderID As Integer = CInt(Request.Params("ID")) ' Get the customer ID too Dim myNewCustomerId As Integer = 0 myNewCustomerId = orderHistory.GetOrderCustomer(OrderID) Public Function GetOrderCustomer(ByVal orderID As Integer) As Integer ' Create Instance of Connection and Command Object Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As SqlCommand = New SqlCommand("nlbsp_OrdersCustomerID", myConnection) ' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure ' Add Parameters to SPROC Dim parameterOrderID As New SqlParameter("@.order_id", SqlDbType.Int, 4) parameterOrderID.Value = orderID myCommand.Parameters.Add(parameterOrderID) Dim parameterOrderCustID As New SqlParameter("@.customer_id", SqlDbType.Int, 4) parameterOrderCustID.Value = ParameterDirection.Output myCommand.Parameters.Add(parameterOrderCustID) 'Open the connection and execute the Command myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() ' Return the customer_id (obtained as out paramter of SPROC) If parameterOrderCustID.Value <> 0 Then Return CInt(parameterOrderCustID.Value) Else Return 0 End If End Functionthe stored procdure isCREATE PROCEDURE [dbo].[nlbsp_OrdersCustomerID]( @.order_id int, @.customer_id int OUTPUT)AS/* Return the customer_id from the Orders. */SELECT @.customer_id = customer_id FROM nlb_Orders WHERE order_id = @.order_idGO

I know a particular order_id returns a value of 1. But when I run it in the webpage it always comes back as 2.

Any ideas would be appreciated

Thanks

Pete

Seems you made a small mistake:

parameterOrderCustID.Value = ParameterDirection.Output

It should be:

Dim CustomerID As Integer
parameterOrderCustID.Value = CustomerID
parameterOrderCustID.Direction=ParameterDirection.Output

incorrect value

Hi,
I'm using crystal reports with firebird database. I installed the firebird ODBC Driver and I
created a DSN connection.
I've in my table a field of type number(15,2) and when I add it in the Crystal report a
incorret value is displayed.
Ex:
Table value field 5,45 and is displayed 0,05 int the crystal report.
The firebird dont have currency type.
number type only.

How I can do for the crystal report to use it number as currencey?

Please, help me.Hi
I dont know about the firebird database.

but please try to insert the value on the filed

like 547 without using comma

regards

Incorrect syntax near comparison on parameter value

Error: Incorrect syntax near '@.today'.

DECLARE @.today CHAR(8)

SET @.today = CONVERT(CHAR(8), GETDATE(), 112)

SELECT c.name,

c.cust,

m.num,

ph.Entered

FROM Master m (NOLOCK)

INNER JOIN dbo.hisy ph ON ph.number = m.num

INNER JOIN dbo.Cust c ON c.Cust = m.Cust

WHERE m.customer IN (0000162, 0000164)

AND ph.Entered IS NOT NULL

AND CONVERT(CHAR(8), ph.Entered, 112) = @.today <-Error is here

It passes syntax checking for me. Is this the entire batch?

If I add a BEGIN before the statement, I get the syntax error two. Sometimes SQL Server error messages are really misleading, though it is technically correct.

|||thanks I was missign the END, that was it.

Incorrect Syntax near \

Hi

I'm trying to lookup a sigle value from an MS SQL Express database. However, I get an error - Incorrect Syntax near '\' - when executing the code. The line in bold below is what is highlighted on the error I get back. Please Help!!

The code is below

Thanks

Dim oSQLConn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection()oSQLConn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SDCentral.mdf;Integrated Security=True;User Instance=True"oSQLConn.Open() Dim ValExtract As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("SELECT * FROM ProjectsQRY", oSQLConn) Dim Get1 As Int32 = CInt(ValExtract.ExecuteScalar()) Me.Active1.Text = Get1

oSQLConn.Close()

Hi,

What is the first column in your output, check it, if its any string column that might be the reason.

|||

Hi

Thanks. The first colomn is IsNull([CNT],0)

|||

Is ProjectsQRY a table or a view?

|||

Hi

It was a view, but I found the problem - it was in the view. I was fooled by the obscure error message. Thanks for your help

Incorrect Subtotal in Matrix

Hi,

I am using the matrix element. in the data part i m using the aggregate function CountDistinct. and when i m taking the subtotal for this value across rows the total is coming wrong. while in the same matrix i have other data values also which uses functions SUM, for these the Subtotal is coming correct.

Can somebody tell me why this is happening with CountDistinct function.

Thanks in advance.

Count will count the number of occurrences of this record in a row or column. It will not add them together. Even if your value is more than 1, it will only count 1 time and add 1 to the total.
SUM adds the values in the rows together. That's why the subtotal will work correctly but CountDistinct not.

Hope this can help,
|||

Thanks for a quick response eduard,

what should i do to get the sum of all the rows where the countdistinct function is used?

|||Any work around on this?|||

the 'sum distinct' workaround might help

look for the whitepaper by 'fang wang' which has this sample in

Incorrect Subtotal in Matrix

Hi,

I am using the matrix element. in the data part i m using the aggregate function CountDistinct. and when i m taking the subtotal for this value across rows the total is coming wrong. while in the same matrix i have other data values also which uses functions SUM, for these the Subtotal is coming correct.

Can somebody tell me why this is happening with CountDistinct function.

Thanks in advance.

Count will count the number of occurrences of this record in a row or column. It will not add them together. Even if your value is more than 1, it will only count 1 time and add 1 to the total.
SUM adds the values in the rows together. That's why the subtotal will work correctly but CountDistinct not.

Hope this can help,
|||

Thanks for a quick response eduard,

what should i do to get the sum of all the rows where the countdistinct function is used?

|||Any work around on this?|||

the 'sum distinct' workaround might help

look for the whitepaper by 'fang wang' which has this sample in

sql

Incorrect sql statement - not reading variable

In the funtcion below I am selecting a value from the page - Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value.

However this value is not then working in the following line:

strSQL ="SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"

This statement is not reading any value for ProjectID and so is selecting all from the table....

How do I write this statement to pick up the variable above???

Thanks in advance for your help!

Clare

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

Dim dsAsNew Data.DataSet

Dim daAs Data.SqlClient.SqlDataAdapter

Dim strSQLAsString

Dim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"Dim connStringAsString ="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"

da =

New Data.SqlClient.SqlDataAdapter(strSQL, connString)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

ForEach tempRowAs Data.DataRowIn ds.Tables(0).Rows

tempRow.Item(

"imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID"))Next

ImgGrid3.DataSource = ds

ImgGrid3.DataBind()

EndSub

hi,

try this

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = @.projectID"

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =ProjectID
p.SqlDbType =System .Data .SqlDbType .Char ;

yourcommand.Pramaters.Add(p)

|||

Thanks for your quick response but this is not working. I do not have a sql command declared, I am using strSQL ( a string), and da (a sqldataadaptor).

Can you help??

Thanks very much

|||

hi cabby

use following

Dim da As Data.SqlClient.SqlDataAdapterDim strSQL As StringDim ProjectID As Int32 ProjectID = Request.QueryString("ProjectID") strSQL ="SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = @.ProjectID" dim sqlcmdas new sqlcommand(strsql, connstring)sqlcmd.parameter.addwithvalue("ProjectID", request.querystring("projectid"));Dim connString As String ="Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" da = New Data.SqlClient.SqlDataAdapter(sqlcmd) da.Fill(ds)in case your project idis char/varchar field use followingas parametervaluesqlcmd.parameter.addwithvalue("ProjectID","'" + request.querystring("projectid") +"'" ) 'see its included in single quotes

HTH

satish.

|||Hiya,|||

Hiya,

Thanks for the response. Sorry if I am asking simple questions but I have tried your code and am getting the following error:

Dim

sqlcmdAsNew sqlcommand(strSQL, connstring) - - - type 'sqlcommand' is not defined... so I change this to

Dim

sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL, connString) -- and then I get connString - value of type string cannot be converted to 'System.Data.SqlClient.SqlConnection' and I also get an error on the next line

sqlcmd.parameter.addwithvalue(

"ProjectID", Request.QueryString("projectid")) -- parameter is not a member of 'System.Data.SqlClient.SqlConnection'

My code now is:

Dim

dsAsNew Data.DataSetDim daAs Data.SqlClient.SqlDataAdapterDim strSQLAsStringDim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [@.ProjectID]"Dim sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL, connString)

sqlcmd.parameter.addwithvalue(

"ProjectID", Request.QueryString("projectid"))Dim connStringAsString ="xxxxxxxxx"

da =

New Data.SqlClient.SqlDataAdapter(sqlcmd)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

Thanks for the help!

Clare

|||first of all make sure you've imported system.data.sqlclient.

DimdsAsNew Data.DataSet

Dim daAs Data.SqlClient.SqlDataAdapter

Dim strSQLAsString

Dim ProjectIDAs Int32

ProjectID = Request.QueryString(

"ProjectID")

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [@.ProjectID]"

Dim sqlcmdAsNew Data.SqlClient.SqlCommand(strSQL,new sqlconnection(connString) )

sqlcmd.parameters.addwithvalue(

"ProjectID", ProjectID) //make sure variable projectid is not null

Dim connStringAsString ="xxxxxxxxx"

da =

New Data.SqlClient.SqlDataAdapter(sqlcmd)

da.Fill(ds)

ds.Tables(0).Columns.Add(

"imgFile")

hope it will help now.

thanks,

satish.

|||

Thanks again, really appreciate the help.

Nearly there now I think...

I am getting to the line:

da.Fill(ds)

and getting the error:

Invalid column name'@.ProjectID'.

Any ideas?

Thanks

Clare

|||

hi,

try this

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =ProjectID
p.SqlDbType =System .Data .SqlDbType .Char ;

sqlcmd.Pramaters.Add(p)

|||

errr sorry appologies for silly mistake of me change following line of my code

sqlcmd.parameters.addwithvalue("ProjectID", ProjectID) //make sure variable projectid is not null

to

sqlcmd.parameters.addwithvalue("@.ProjectID", ProjectID) //make sure variable projectid is not null

thanks,

satish.

|||

Hi,

I am still getting the same error. Do I need to declare in my sub function header that it is taking in a parameter? And if so how do I write this on page load?

Thanks

Clare

|||

After all this I can just use:

strSQL =

"SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] =" & Request.QueryString("ProjectID")

Thanks for all the help

|||

hi

try this, it works fine

Dim p As New System.Data.SqlClient.SqlParameter()
p.ParameterName = "@.projectID"
p.Value =Request.QueryString("ProjectID")

p.SqlDbType =Data .SqlDbType .String;

sqlcmd.Pramaters.Add(p)

Incorrect data in sysdatabases compatability level column

Hello,
I've noticed that if you have model and tempdb to different compatability
levels and restart the sql instance, the value in sysdatabases (cmptlevel)
does not get changed correctly.
Here's how to reproduce:
1. set model compatability to one level, say 9.0
2. set tempdb compatability to diff. level, say 8.0
3. verify in SSMS db -> properties -> options
or select * from master..sysdatabases
4. run an old join query from tempdb to show it is actually in 8.0
compatability
-> select * from sysobjects o, syscolumn c where o.id *= c.id
if it runs, the compatability is set to 8.0
5. now restart your db instance
6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
8.0 compatability, but you get an error message when running the query which
means the database is in 9.0
Can anyone explain? or is it possible this is a bug?
thanksPlease ignore this one, I resubmitted this same ticket the to SQL Server
category of threads.
"paul" wrote:

> Hello,
> I've noticed that if you have model and tempdb to different compatability
> levels and restart the sql instance, the value in sysdatabases (cmptlevel)
> does not get changed correctly.
> Here's how to reproduce:
> 1. set model compatability to one level, say 9.0
> 2. set tempdb compatability to diff. level, say 8.0
> 3. verify in SSMS db -> properties -> options
> or select * from master..sysdatabases
> 4. run an old join query from tempdb to show it is actually in 8.0
> compatability
> -> select * from sysobjects o, syscolumn c where o.id *= c.id
> if it runs, the compatability is set to 8.0
> 5. now restart your db instance
> 6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is
in
> 8.0 compatability, but you get an error message when running the query whi
ch
> means the database is in 9.0
> Can anyone explain? or is it possible this is a bug?
> thanks
>
>

Incorrect data in sysdatabases compatability level column

Hello,
I've noticed that if you have model and tempdb to different compatability
levels and restart the sql instance, the value in sysdatabases (cmptlevel)
does not get changed correctly.
Here's how to reproduce:
1. set model compatability to one level, say 9.0
2. set tempdb compatability to diff. level, say 8.0
3. verify in SSMS db -> properties -> options
or select * from master..sysdatabases
4. run an old join query from tempdb to show it is actually in 8.0
compatability
-> select * from sysobjects o, syscolumn c where o.id *= c.id
if it runs, the compatability is set to 8.0
5. now restart your db instance
6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
8.0 compatability, but you get an error message when running the query which
means the database is in 9.0
Can anyone explain? or is it possible this is a bug?
thanks
Sorry, I forgot to tell you to run the test query below from the tempdb to
either produce an error message or not. The connection's current db
determines compatability level.
"paul" wrote:

> Hello,
>
> I've noticed that if you have model and tempdb to different compatability
> levels and restart the sql instance, the value in sysdatabases (cmptlevel)
> does not get changed correctly.
> Here's how to reproduce:
> 1. set model compatability to one level, say 9.0
> 2. set tempdb compatability to diff. level, say 8.0
> 3. verify in SSMS db -> properties -> options
> or select * from master..sysdatabases
> 4. run an old join query from tempdb to show it is actually in 8.0
> compatability
> -> select * from sysobjects o, syscolumn c where o.id *= c.id
> if it runs, the compatability is set to 8.0
> 5. now restart your db instance
> 6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
> 8.0 compatability, but you get an error message when running the query which
> means the database is in 9.0
> Can anyone explain? or is it possible this is a bug?
> thanks
>
>
|||For what it is worth, I would not mess with the compatibilty level of
either tempdb or model. I know the idea is that you can set model to
match what you want from a new database, but as tempdb is effectively
recreated from model each time SQL Server starts it sounds like asking
for trouble.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 11:14:08 -0700, paul
<paul@.discussions.microsoft.com> wrote:

>Hello,
>
>I've noticed that if you have model and tempdb to different compatability
>levels and restart the sql instance, the value in sysdatabases (cmptlevel)
>does not get changed correctly.
>Here's how to reproduce:
>1. set model compatability to one level, say 9.0
>2. set tempdb compatability to diff. level, say 8.0
>3. verify in SSMS db -> properties -> options
>or select * from master..sysdatabases
>4. run an old join query from tempdb to show it is actually in 8.0
>compatability
> -> select * from sysobjects o, syscolumn c where o.id *= c.id
> if it runs, the compatability is set to 8.0
>5. now restart your db instance
>6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
>8.0 compatability, but you get an error message when running the query which
>means the database is in 9.0
>Can anyone explain? or is it possible this is a bug?
>thanks
>

Incorrect data in sysdatabases compatability level column

Hello,
I've noticed that if you have model and tempdb to different compatability
levels and restart the sql instance, the value in sysdatabases (cmptlevel)
does not get changed correctly.
Here's how to reproduce:
1. set model compatability to one level, say 9.0
2. set tempdb compatability to diff. level, say 8.0
3. verify in SSMS db -> properties -> options
or select * from master..sysdatabases
4. run an old join query from tempdb to show it is actually in 8.0
compatability
-> select * from sysobjects o, syscolumn c where o.id *= c.id
if it runs, the compatability is set to 8.0
5. now restart your db instance
6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
8.0 compatability, but you get an error message when running the query which
means the database is in 9.0
Can anyone explain? or is it possible this is a bug?
thanks
Please ignore this one, I resubmitted this same ticket the to SQL Server
category of threads.
"paul" wrote:

> Hello,
> I've noticed that if you have model and tempdb to different compatability
> levels and restart the sql instance, the value in sysdatabases (cmptlevel)
> does not get changed correctly.
> Here's how to reproduce:
> 1. set model compatability to one level, say 9.0
> 2. set tempdb compatability to diff. level, say 8.0
> 3. verify in SSMS db -> properties -> options
> or select * from master..sysdatabases
> 4. run an old join query from tempdb to show it is actually in 8.0
> compatability
> -> select * from sysobjects o, syscolumn c where o.id *= c.id
> if it runs, the compatability is set to 8.0
> 5. now restart your db instance
> 6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
> 8.0 compatability, but you get an error message when running the query which
> means the database is in 9.0
> Can anyone explain? or is it possible this is a bug?
> thanks
>
>

Incorrect data in sysdatabases compatability level column

Hello,
I've noticed that if you have model and tempdb to different compatability
levels and restart the sql instance, the value in sysdatabases (cmptlevel)
does not get changed correctly.
Here's how to reproduce:
1. set model compatability to one level, say 9.0
2. set tempdb compatability to diff. level, say 8.0
3. verify in SSMS db -> properties -> options
or select * from master..sysdatabases
4. run an old join query from tempdb to show it is actually in 8.0
compatability
-> select * from sysobjects o, syscolumn c where o.id *= c.id
if it runs, the compatability is set to 8.0
5. now restart your db instance
6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
8.0 compatability, but you get an error message when running the query which
means the database is in 9.0
Can anyone explain? or is it possible this is a bug?
thanksSorry, I forgot to tell you to run the test query below from the tempdb to
either produce an error message or not. The connection's current db
determines compatability level.
"paul" wrote:
> Hello,
>
> I've noticed that if you have model and tempdb to different compatability
> levels and restart the sql instance, the value in sysdatabases (cmptlevel)
> does not get changed correctly.
> Here's how to reproduce:
> 1. set model compatability to one level, say 9.0
> 2. set tempdb compatability to diff. level, say 8.0
> 3. verify in SSMS db -> properties -> options
> or select * from master..sysdatabases
> 4. run an old join query from tempdb to show it is actually in 8.0
> compatability
> -> select * from sysobjects o, syscolumn c where o.id *= c.id
> if it runs, the compatability is set to 8.0
> 5. now restart your db instance
> 6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
> 8.0 compatability, but you get an error message when running the query which
> means the database is in 9.0
> Can anyone explain? or is it possible this is a bug?
> thanks
>
>|||For what it is worth, I would not mess with the compatibilty level of
either tempdb or model. I know the idea is that you can set model to
match what you want from a new database, but as tempdb is effectively
recreated from model each time SQL Server starts it sounds like asking
for trouble.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 11:14:08 -0700, paul
<paul@.discussions.microsoft.com> wrote:
>Hello,
>
>I've noticed that if you have model and tempdb to different compatability
>levels and restart the sql instance, the value in sysdatabases (cmptlevel)
>does not get changed correctly.
>Here's how to reproduce:
>1. set model compatability to one level, say 9.0
>2. set tempdb compatability to diff. level, say 8.0
>3. verify in SSMS db -> properties -> options
>or select * from master..sysdatabases
>4. run an old join query from tempdb to show it is actually in 8.0
>compatability
> -> select * from sysobjects o, syscolumn c where o.id *= c.id
> if it runs, the compatability is set to 8.0
>5. now restart your db instance
>6. repeat step 3 and 4, you should see that sysdatabases thinks tempdb is in
>8.0 compatability, but you get an error message when running the query which
>means the database is in 9.0
>Can anyone explain? or is it possible this is a bug?
>thanks
>