ASP and SQL driving me nuts

[BezerkRogue]

I have gotten my pages to work up to the final edit page. The code executes on load. Variables are passed by session but I get an SQL syntax error. I've tested the sp I'm calling and it works correctly. Here's the code"

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArg s) Handles Me.Load
Dim strSelDate
Dim strShift
Dim strAbsent
Dim strTO
Dim strHeadCount
Dim strSTDHeadCount
Dim strMPNotes
Dim Notes2
Dim strSQL
Dim strRID

strRID = Session.Item("R ID").ToString ()
strSelDate = Session.Item("S elDate").ToStri ng()
strShift = Session.Item("S hift").ToString ()
strAbsent = Session.Item("A bsent").ToStrin g()
strTO = Session.Item("T O").ToString ()
strHeadCount = Session.Item("H eadCount").ToSt ring()
strSTDHeadCount = Session.Item("S TDHeadCount").T oString()
strMPNotes = Session.Item("N otes").ToString ()
'Notes2 = strMPNotes.Repl ace("'", "''")
strSQL = "UP_MANPOWER_RE CORD_UPDATE '" & strSelDate & "', '" & strShift & "', " & strHeadCount & ", " & strSTDHeadCount & ", " & strAbsent & ", " & strTO & ", '" & Notes2 & "', " & strRID
Dim objConn As New Data.SqlClient. SqlConnection(" Data Source=XXXXXX;I nitial Catalog=XXXXXX; User ID=XXXXXX;passw ord=XXXXXX;")
Dim objCmd As New Data.SqlClient. SqlCommand(strS QL, objConn)
objConn.Open()
objCmd.ExecuteN onQuery()
objConn.Close()

End Sub

Here's the error I get:

System.Data.Sql Client.SqlExcep tion: Line 1: Incorrect syntax near '.'.

Can anyone enlighten this drowning noob??

[Plater]

Are you properly dealing with single quotes in your text?
You need to do a quote replace on all text you try to insert.
so a ' needs to become two of them '' (Note: not a " but ' ' without the space)

[BezerkRogue]

Are you properly dealing with single quotes in your text?
You need to do a quote replace on all text you try to insert.
so a ' needs to become two of them '' (Note: not a " but ' ' without the space)

This line replaces single quotes with double quotes in the notes string.

Notes2 = strMPNotes.Repl ace("'", "''")

[Plater]

Well, it's commented out in your original post for one thing.

And are you doing it for all your other strings too?

[BezerkRogue]

I uncommented it after I posted here. That is the only place where the operator can enter any text data. That is the only field that I have this for.

[Plater]

if any of those sql columns are supposed to be numbers (and not strings) you should NOT be using ' around the values.
Also, are any of those values being stored as a double? or anything that might have a decimal point?

[BezerkRogue]

No. The fields are numeric with the exception of Date, and Notes. There is a sub select in the stored procedure the converts the text data for shift to the correct numeric format from a separate table.

[Plater]

Well then remove the ' around the values for the numeric fields, the ' should only be used on text fields

[BezerkRogue]

Well then remove the ' around the values for the numeric fields, the ' should only be used on text fields

There are no ' around any numeric fields in the command.

[Plater]

There are no ' around any numeric fields in the command.

hehe oops, my mistake, I didn't look at the variable name close enough to see it said it was Date.

If you set a breakpoint here:

Dim objConn As New Data.SqlClient.SqlConnection("Data Source=XXXXXX;Initial Catalog=XXXXXX;User ID=XXXXXX;password=XXXXXX;")
 

what does the value of your sql query strSQL, look like? Maybe there's something that can be caught by seeing how it is displayed?
By the way, is that a stored procedure you're trying to run there?