Can anyone please tell me what is wrong with this query

Can anyone please tell me what is wrong with this query:

rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")

In DreamWeaver the bit in bold is greyed out, why?

rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")

Thanks Joe

Hey Joe,

you're missing some( + " ), try this:

rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") + " AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")

|||

Hikpeguero,

thanks very much for answering, that has stopped the error message but it isnt actually working though...

Im trying to test and see if the current record set is a subcategory in the database, the record im testing is, but it isnt being picked up as one.

This is the basic structure of the table "SubCatagories" im running the query on:

|SubCatagoryID|CatagoryID|"CatagoryName"|
|241|0|"Cat Types"|
|242|0|"Dog Types"|
|243|0|"Fish Types"|
|244|0|"Insect Types"|
|427|243|"Anglers"|
|428|243|"Anthias"|
|429|243|"Basslets"|

The values im trying to check above equate to the query string passed in the URL: page.asp?SubcatagoryID=428

If the current Query string is "428" (Anglers) then it is a subcategory of "243" (Fish Types) as the code loops through the records I need it to check if the query string "428" is associated with the SubCategoryID record being loped through at the time. It should check the Request.QueryString("SubCatagoryID") against the CategoryID of the other records for a match, When it gets to "243" (Fish Types) for example it should find that the current QueryString("SubcatagoryID") of 428 maches, because the CatagoryID in that record matches the (Fish Types) SubCatagoryID.

rsSubCatagories__MMColParam = 428 and rsOtherSubCatagories__MMColParam = 243 Thus the query:

rsOtherSubCatagories.Source = "SELECT * FROM SubCatagories WHERE SubCatagoryID = " + Replace(rsSubCatagories__MMColParam, "'", "''") + " AND CatagoryID = " + Replace(rsOtherSubCatagories__MMColParam, "'", "''")

What is the best way to test for this, should I open a new database connection and run a query for each record? And if so how would I construct the query to check this?

Thanks for your help, Joe

Can anyone help and tell me what i am doing wrong?

Here is the code

Line 84:
Line 85: searchDataAdapter = New System.data.sqlclient.sqldataadapter("SELECT * FROM Inventory Where title=" & title, searchConnection)
Line 86: searchDataAdapter.Fill(objItemInfo, "ItemInfo")
Line 87:
Line 88: Return objItemInfo

here is the error
Line 1: Incorrect syntax near '='.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.

Source Error:

Try adding single quotes:

searchDataAdapter = New System.data.sqlclient.sqldataadapter("SELECT * FROM Inventory Where title='" & title & "'", searchConnection)

HTH,
Ryan

|||Hey Thanks, it worked....I knew it was something simple like that|||

try this

SELECT * FROM Inventory Where title=' " & title & " '

can any body tell me what wrong in my sql statement

Hi,

can any body tell me what is wrong in my sql statement

SELECT title, price,

Budget

=CASE priceWHEN price> 20.00THEN'Expensive'WHEN priceBETWEEN 10.00AND 19.99THEN'Moderate'WHEN price< 10.00THEN'Inexpensive'ELSE'Unknown'END

FROM

titlesit gives me this error

Msg 170, Level 15, State 1, Line 3

Line 3: Incorrect syntax near '>'.

but when i use somthing like that i will works fine

SELECT

Budget

i am using sql server 2000

SELECT title, price, Budget=CASEWHEN price < 10.00THEN'Inexpensive'WHEN price < 20.00THEN'Moderate'WHEN priceISNULLTHEN'Unknown'ELSE'Expensive'ENDFROMtitles

I'm assuming that price is a Decimal datatype.

|||

Take the price after CASE out. There are two forms of CASE statement. Your syntax was wrong. 

SELECT title, price, Budget=CASE

WHEN price> 20.00THEN'Expensive'

WHEN

priceBETWEEN 10.00AND 19.99THEN'Moderate'

WHEN

price< 10.00THEN'Inexpensive'

ELSE

'Unknown'

END

FROMtitles

Can .net 1.1 work with SQL SERVER 2005 ?

What should I do if I want my asp.net 1.1 work with sql server 2005

I get wrong message :SQL Server does not exist or access denied

Well, you don't need to change any code.. there is no different of working against a SQL 2005, it's the same way as working against SQL server 2000 etc.

If you have some problem to connect to the SQL Server 2005, just check your connection string if it's correct. For example if you provide the right user name and password etc. The connection string for connecting to a SQL Server 2000 and SQL Server 2005 is the same..

|||

Hi,

Yes of course it can work with .Net 1.1. Please make sure that the connection string is correct. If you are using SqlExpress the instance name of the SQL 2005 is MachineName\SQLEXPRESS.

|||

Yes it is work in my boxdev, but id does not work in my web hosting.

I try it in my hosting to set using 2.0 and work fine, but when I use 1.1 it is like the server does not existed. The server name using IP etc xxx.xxx.xxx.xxx. And it work fine too when I used in remote connection using sql server management studio.

So what make this 1.1 can not fine or denied access to sql server. You know I 'm have used right connection string.

|||

I have check that the hosting use sql server 2005 express. Is there a problem when using 1.1 with 2005 express ? Because it is just fine in 2005 developer ?

|||

Hi,

You may try this KB article:http://support.microsoft.com/kb/928319/en-us It indicates the main causes of the problem and provide some solutions for you to refer.

Hopt that helps. Thanks.

Calling variable inside T-SQL statement

Can someone please take a quick look at this and tell me what I'm doing wrong I'm sure it's something simple. I'm a little new to stored procedures but I've been using SQL and T-SQL for quite some time, I've just always used inline queries with my ASP. This procedure needs to be run monthly by me or another person I grant access to and will update sales information that our sales staff will be paid commission on. I need to supply the start date and and end date for the query and it will pull this information from our business system which is hosted remotely by a third party and pull it into our local SQL server where we can run commission reports against it. (I hope this is enough information you can understand where I'm trying to go with this). I know my problem right now lies in how I'm trying to call the variable inside of my T-SQL. Any help is appreciated. This is an old Unix system and it stores the date as YYYYMMDD as numeric values incase someone wonders why I have dimed my dates as numeric instead of as datetime =)

I'm using a relativity client to create an ODBC connection to the UNIX server and then using a linked server to map a connection in SQL this is the reason for the OpenQuery(<CompanyName>

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

-- =============================================

-- Author: XXXXXXXXXXXXX

-- Create date: 10/4/2007

-- Description: This proc is designed to pull all CSA

-- part sales from XXXXXX business system and upload them

-- into the local XXXXXXXX Database for commission reporting

-- =============================================

CREATEproc usp_CSAPartsSalesUpdate@.date1int, @.date2int

As

INSERTINTO CSAPartsSales( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount)

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmtFromOpenQuery(<CompanyName>,'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE (((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>='''+ @.date1+''' And (PMINVHST.HInvDate)<='''+ @.date2+''') AND ((Trim([CPBASC_All].[SalesRoute]))<>'''' And (Trim([CPBASC_All].[SalesRoute]))<>''000''))')

In this example date1 will be equal to 20070901 and date2 will be equal to 20070930 so I can pull all CSA sales for the month of September.

This is the error message I get when I try to create the proc:

Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17

Incorrect syntax near '+'.

~~~ Thanks All~~~

You need to cast your variable as a varchar, ie:

>=''' + CAST(@.date1 as varchar(8)) + ''' AND ( ...etc

|||

Thanks for the reply Sswanner1 I'll try casting my variables tomorrow when I get into work and see if that corrects the problem.

|||

I tried casting my variables this morning as you suggested but it still returns the same error message

((PMINVHST.HInvDate)>='''+Cast(@.date1asvarchar(8))+''' And (PMINVHST.HInvDate)<='''+Cast(@.date2asvarchar(8))+''') AND

Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17

Incorrect syntax near '+'.

|||

If PMINVHST.HInvDate is also an integer, you've got too many single quotes in there. Ultimately, you want it to look like this:

((PMINVHST.HInvDate)>=20070810, not ((PMINVHST.HInvDate)>='20070810'

|||

PMINVHST.HInvDate is stored as text in the unix system

If I take the exact same sql statement and declare the variables and then set the values and then print the results the query looks exactly like I need it to. I can take the results that print to the screen copy them into a query edititor and run the query and I get the results I'm looking for. It has something to do with the way I'm trying to call it...I can't put my finger on it =(

|||

If I run this SP and then copy and paste the results to query editor it returns exactly what I need.

Declare @.commandasnvarchar(4000)

Declare @.date1asvarchar(8)

Declare @.date2asvarchar(8)

Set @.date1= 20070901

Set @.date2= 20070930

Set @.command='INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(XXXXXXXX, ''Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE ((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>='''''+ @.date1+''''' and (PMINVHST.HInvDate)<='''''+ @.date2+''''') AND (Trim([CPBASC_All].[SalesRoute])<>'''''''' And Trim([CPBASC_All].[SalesRoute])<>''''000'''')'')'

Print @.command

Print Results:

INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(XXXXXXXXX, 'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE ((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>=''20070901'' and (PMINVHST.HInvDate)<=''20070930'') AND (Trim([CPBASC_All].[SalesRoute])<>'''' And Trim([CPBASC_All].[SalesRoute])<>''000'')')

|||

I just copied and pasted your code and ran the query and got the same printed results. One thing I noticed is that it's putting 2 single quotes before and after each of your dates. Is this what you are wanting? Or are you wanting a regular quote? Or just a single, single quote?

'20070901'

''20070901''

"20070901"

|||

For everyone reading this if you ever run into a problem trying to use OpenQuery in an SP be aware that OpenQuery does not play nice. If you're problem is similar please try running your SP using this method. I battled this for a while and this was the only way I could make it work. OpenQuery does not like taking expressions and parameters directly.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

-- =============================================

-- Author:

-- Create date: 10/4/2007

-- Description: This proc is designed to pull all CSA

-- part sales from XXXXX business system and upload them

-- into the local XXXXXX Database for commission reporting

-- =============================================

ALTERproc [dbo].[usp_CSAPartsSalesUpdate]

(

@.date1varchar(8),

@.date2varchar(8)

)

As

Declare @.commandasnvarchar(4000)

Set @.command='INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(LinkedServerName, ''Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE ((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>='''''+ @.date1+''''' and (PMINVHST.HInvDate)<='''''+ @.date2+''''') AND (Trim([CPBASC_All].[SalesRoute])<>'''''''' And Trim([CPBASC_All].[SalesRoute])<>''''000'''')'')'

Exec(@.command)

|||

Thanks Sswanner1 for all your efforts!!