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??

[BezerkRogue]

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?

Yeah. Here's the code for the SP:

ALTER PROCEDURE UP_MANPOWER_REC ORD_UPDATE
@Date datetime,
@Shift varchar(10),
@HeadCount int,
@STDHeadCount int,
@Absent int,
@TO int,
@Notes varchar(1000),
@RID int
AS
Update Manpower_Data
set Date = @Date, Shift_ID = (select shift_id from shift where shift=@Shift) , HeadCount = @HeadCount, STDHeadCount= @STDHeadCount, Absent= @Absent, [TO] = @TO, Notes= @Notes
where RID = @RID

[Plater]

ah ha! That's why. I suspected it from the begining and shoulda listened to myself.

That's not how you call stored procedures in .NET.

Here is a (long) example taken directly from one of my projects.
dbcon is the name of my open dbconnection
"InsertNewBoard Assembly" is the name of my stored procedure. You can see how parameters are added, including the OUTPUT paramter.

Public Shared Function InsertNewBoardAssembly(ByVal Name As String, ByVal BoardAssemblyNumber As String, ByVal RevNumber As Integer, ByVal BOMID As Int64, ByVal PartID As Int64, ByVal Notes As String) As Int64 
    Dim retval As Int64 = -1 
    Dim logmsg As String = "" 
 
    Try 
        Dim ModifiedDate As DateTime = DateTime.Now 
        Dim sc As New SqlCommand("InsertNewBoardAssembly", dbcon) 
        sc.CommandType = CommandType.StoredProcedure 
 
        sc.Parameters.Add("@Name", SqlDbType.VarChar) 
        sc.Parameters("@Name").Value = quoteReplace(Name) 
 
        sc.Parameters.Add("@BoardAssemblyNumber", SqlDbType.VarChar) 
        sc.Parameters("@BoardAssemblyNumber").Value = quoteReplace(BoardAssemblyNumber) 
 
        sc.Parameters.Add("@RevNumber", SqlDbType.Int) 
        sc.Parameters("@RevNumber").Value = RevNumber 
 
        sc.Parameters.Add("@BOMID", SqlDbType.BigInt) 
        sc.Parameters("@BOMID").Value = BOMID 
 
        sc.Parameters.Add("@PartID", SqlDbType.BigInt) 
        sc.Parameters("@PartID").Value = PartID 
 
        sc.Parameters.Add("@Notes", SqlDbType.VarChar) 
        sc.Parameters("@Notes").Value = quoteReplace(Notes) 
 
        sc.Parameters.Add("@ModifiedDate", SqlDbType.DateTime) 
        sc.Parameters("@ModifiedDate").Value = ModifiedDate 
 
        'out 
        sc.Parameters.Add("@BoardAssemblyID", SqlDbType.BigInt) 
        sc.Parameters("@BoardAssemblyID").Direction = ParameterDirection.Output 
        Dim numrows As Integer = sc.ExecuteNonQuery() 
        retval = DirectCast(sc.Parameters("@BoardAssemblyID").Value, Int64) 
        sc.Dispose() 
    Catch ee As Exception 
        ReportError("InsertNewBoardAssembly", ee) 
    End Try 
    Return retval 
End Function 
 

[BezerkRogue]

What does this error mean?

BC31029: Method 'Protected Sub Page_Load(sende r As Object, e As System.EventArg s, rDate As Date, Shift As String, HeadCount As Integer, STDHeadCount As Integer, Absent As Short, rTO As Integer, Notes As String, RID As Integer)' cannot handle Event 'Public Event Load(sender As Object, e As System.EventArg s)' because they do not have the same signature.

[Plater]

Two of my field names are seen as keywords when I try to enter them in the args portion of the sub routine. How can I correct this. Renaming them in the DB won't work as the number of procedures and reports that read all of this is huge.

Enclose column names (and other database objects) with square brackets
[myTableName].[MyColumnName]=@Somevariable

[BezerkRogue]

Enclose column names (and other database objects) with square brackets
[myTableName].[MyColumnName]=@Somevariable

Ok. Here's what I have:

Dim objCmd As New Data.SqlClient. SqlCommand("UP_ MANPOWER_RECORD _UPDATE", objConn)
objCmd.CommandT ype = Data.CommandTyp e.StoredProcedu re

objCmd.Paramete rs.Add("@rDate" , Data.SqlDbType. DateTime)
objCmd.Paramete rs("@rDate").Va lue = strSelDate

objCmd.Paramete rs.Add("@Shift" , Data.SqlDbType. VarChar)
objCmd.Paramete rs("@Shift").Va lue = strShift

objCmd.Paramete rs.Add("@HeadCo unt", Data.SqlDbType. Int)
objCmd.Paramete rs("@HeadCount" ).Value = strHeadCount

objCmd.Paramete rs.Add("@STDHea dCount", Data.SqlDbType. Int)
objCmd.Paramete rs("@STDHeadCou nt").Value = strSTDHeadCount

objCmd.Paramete rs.Add("@Absent ", Data.SqlDbType. Int)
objCmd.Paramete rs("@Absent").V alue = strAbsent

objCmd.Paramete rs.Add("@rTO", Data.SqlDbType. Int)
objCmd.Paramete rs("@rTO").Valu e = strTO

objCmd.Paramete rs.Add("@Notes" , Data.SqlDbType. VarChar)
objCmd.Paramete rs("@Notes").Va lue = Notes2

objCmd.Paramete rs.Add("@RID", Data.SqlDbType. Int)
objCmd.Paramete rs("@RID").Valu e = strRID

I get System.FormatEx ception: Input string was not in a correct format.
error. Is this due to not having single qoutes around the varchar variables? I put in quoteReplace.st ring name and the app this it's an undeclared variable.

[Plater]

I am guessing it has to do with the date parameter.
When you set the type to Date, the parameter expects you to add an object of type DateTime, not a string?
EDIT: actually the same holds true for all of them, the parameters of type Int, want you to add an object of type Int and not a string.

[Plater]

What does this error mean?

BC31029: Method 'Protected Sub Page_Load(sende r As Object, e As System.EventArg s, rDate As Date, Shift As String, HeadCount As Integer, STDHeadCount As Integer, Absent As Short, rTO As Integer, Notes As String, RID As Integer)' cannot handle Event 'Public Event Load(sender As Object, e As System.EventArg s)' because they do not have the same signature.

it means you accidently deleted something and part of your page_load() function got overriden with the declaration of your other function.
Straighten out the functions and you should be fine.

[BezerkRogue]

it means you accidently deleted something and part of your page_load() function got overriden with the declaration of your other function.
Straighten out the functions and you should be fine.

Got them straightened out. I have remarked out all of the variables in the sp calls and executed it to see where I get a error. On the headcount variable I get a Object must implement IConvertible. error message. The first two pass fine.

Is this a bug or is there something that I didn't put in the code?

[Plater]

strHeadCount is a string yes?
Convert it to an int with:
Int.Parse(strHe adCount)

[BezerkRogue]

strHeadCount is a string yes?
Convert it to an int with:
Int.Parse(strHe adCount)

It didn't like that line. Is there another way to cast a string to an int in VB.Net?