is this query right - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

is this query right

Author

Topic

amitbadgi
Starting Member

29 Posts

Posted - 2005-09-17 : 21:18:24

Hi guys I am working on an vb.net application and useing a query in the code, and i am gettng a syntax error for the following sql statement, could anyone please fgure out whats wrong with this statement.

TEMP = "INSERT INTO tblEvent (case_no, offender_id, STU_ID, date_of_event, event_time, referred_by, "

TEMP= TEMP & " referred_by_other, referred_when, zone, location, spec_location, comments, entered_by, lm_by, entered_when, lm_when, arrest,offendertype) VALUES "
TEMP= TEMP & "('"& case_no & "',"& offender_id &" , '" & stu_id & "', '" & trim(Request("txtDateOfIncident")) & "', '" &
trim(Request("txtTimeOfIncident"))
TEMP= TEMP & "', '" & trim(Request("cboReferred")) & "', "
TEMP= TEMP & " " & OtherReferred & ", " & rdate
TEMP= TEMP & ", '" & trim(Request("zoneSel")) & "', '" & trim(Request("cboLocation")) & "', "
TEMP= TEMP & " NULL, '" & tComments & "', '" & Session("userid") & "', '" & Session("userid") & "', "
TEMP= TEMP & "getdate()" & ", " & "getdate()" & ", " & arrested & ", " & o_type & "); "

--------------------------------------------------------------

Thanks in advance.

Kristen
Test

22859 Posts

Posted - 2005-09-18 : 02:19:03

When this happens to me I "output" the working variable ("TEMP" in your case) and put the resulting SQL into Query Analyser to see what it has to see -its much easier to work with that to get it right than concatenations which might go wrong.

Having said that i don't think you can put GetDate() in a VALUES statement can you? And I don't know what "rdate" contains, but if its a date presumably it will need to have surrounding quotes, and presumable "OtherReferred" is a number (and will not be an empty string)

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-18 : 14:03:21

Stored procedures are a good thing.

/rant

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

amitbadgi
Starting Member

29 Posts

Posted - 2005-09-18 : 14:05:30

Thanx for the reply guys, but I need this to work atleast on temorary basis, can anyone plz help me fix this so that it can run temporarily atleast..thanx again

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-18 : 14:09:27

Are you getting a compile error in your code or SQL syntax error when you send this to SQL Server? If the latter, copy the final output you're sending to SQL Server and paste it into here. We can then troubleshoot quickly.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

amitbadgi
Starting Member

29 Posts

Posted - 2005-09-18 : 17:00:06

HEy thanx for your reply, here is teh code,

TEMP = "INSERT INTO tblEvent (case_no, offender_id, STU_ID, date_of_event, event_time, referred_by, "

TEMP= TEMP & " referred_by_other, referred_when, zone, location, spec_location, comments, entered_by, lm_by, entered_when, lm_when, arrest,offendertype) VALUES "
TEMP= TEMP & "('"& case_no & "',"& offender_id &" , '" & stu_id & "', '" & trim(Request("txtDateOfIncident")) & "', '" &
trim(Request("txtTimeOfIncident"))
TEMP= TEMP & "', '" & trim(Request("cboReferred")) & "', "
TEMP= TEMP & " " & OtherReferred & ", " & rdate
TEMP= TEMP & ", '" & trim(Request("zoneSel")) & "', '" & trim(Request("cboLocation")) & "', "
TEMP= TEMP & " NULL, '" & tComments & "', '" & Session("userid") & "', '" & Session("userid") & "', "
TEMP= TEMP & "getdate()" & ", " & "getdate()" & ", " & arrested & ", " & o_type & "); "

it says there s syntax error in the insert into statement, and i am using it against an access database. Any help is appreciated

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-09-18 : 17:09:26

Are you getting a compile error in your code or SQL syntax error when you send this to SQL Server or Access? If the latter, copy the final output you're sending to SQL Server or Access and paste it into here. We can then troubleshoot quickly.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

amitbadgi
Starting Member

29 Posts

Posted - 2005-09-18 : 19:10:33

Hello,this is the folowing error I am getting,

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30201: Expression expected.

Source Error:

Line 163:
Line 164:TEMP= TEMP & " referred_by_other, referred_when, zone, location, spec_location, comments, entered_by, lm_by, entered_when, lm_when, arrest,offendertype) VALUES "
Line 165:TEMP= TEMP & "('"& case_no & "',"& offender_id &" , '" & stu_id & "', '" & trim(Request("txtDateOfIncident")) & "', '" &
Line 166:trim(Request("txtTimeOfIncident"))
Line 167:TEMP= TEMP & "', '" & trim(Request("cboReferred")) & "', "

Source File: C:\Documents and Settings\Raju\Desktop\WebSite1\add_event_save.aspx Line: 165

Any help is appreciated. Thanks

magesh
Starting Member

23 Posts

Posted - 2005-09-18 : 23:18:04

Hi all,

your query formation statements are correct.
it seems that your .net application syntax is giving problem
(i think it is better confirm with Request() statements and also variables).

kristen :
>>i don't think you can put GetDate() in a VALUES statement can you?

we can use GetDate() in values statements it wont be a problem.

but GetDate() is an SQL server function it wont work in access database.

Magesh

magesh
Starting Member

23 Posts

Posted - 2005-09-18 : 23:24:37

Hi ,

in Line 165: u failed to include on underscore (_).

ur code has to be like this.
temp = "INSERT INTO tblEvent (case_no, offender_id, STU_ID, date_of_event, event_time, referred_by, "

temp = temp & " referred_by_other, referred_when, zone, location, spec_location, comments, entered_by, lm_by, entered_when, lm_when, arrest,offendertype) VALUES "
temp = temp & "('" & case_no & "'," & offender_id & " , '" & stu_id & "', '" & Trim(Request("txtDateOfIncident")) & "', '" & _
Trim(Request("txtTimeOfIncident"))
temp = temp & "', '" & Trim(Request("cboReferred")) & "', "
temp = temp & " " & OtherReferred & ", " & rdate
temp = temp & ", '" & Trim(Request("zoneSel")) & "', '" & Trim(Request("cboLocation")) & "', "
temp = temp & " NULL, '" & tComments & "', '" & Session("userid") & "', '" & Session("userid") & "', "
temp = temp & "getdate()" & ", " & "getdate()" & ", " & arrested & ", " & o_type & "); "

Magesh

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-19 : 11:51:45

If you are using .NET to write this:
1) You should REALLY use a Stringbuilder object instead of all that concatenation.
2) This should be a Stored Procedure. You are doing an INSERT into the database. MASSIVE Security hole. If I get ahold of your connection string, i will drop your DB.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources