commandtext in cmd.executenonquery

[sdeepak]

Please help me debug.Here is my problem




Public Function CreateInsertCommand() As SqlCommand
Dim strsql As String
strsql = "INSERT INTO MCS_CURRENT ( COMPANYCODE , EMPLOYEENO , COSTCENTER , LEADID , WORKSCHEDULEDATE , JOBNO , OPERATION , LINEHOURS , DATEIN, TIMEIN , DATEOUT , TIMEOUT ,ETCIN , ETCOUT , EQUIPMENTNO , UPDATETIME ) VALUES ( ? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) "
Dim CMD As New SqlCommand(strsql, MyConnection)
Dim PC As SqlParameterCollection = CMD.Parameters
PC.Add("COMPANYCODE_NEW", SqlDbType.Int)
PC.Add("EMPLOYEENO_NEW", SqlDbType.BigInt)
PC.Add("COSTCENTER_NEW", SqlDbType.BigInt)
.....
......
PC.Add("UPDATETIME_NEW", SqlDbType.DateTime)
Return CMD
End function

Public Function submitInsert(ByVal row As DataRow, ByVal cmd As SqlCommand) As Integer
Dim PC As SqlParameterCollection = cmd.Parameters
PC("COMPANYCODE_NEW").Value = row("COMPANYCODE")
Console.WriteLine(row("COMPANYCODE"))
PC("EMPLOYEENO_NEW").Value = row("EMPLOYEENO")
....
.....
PC("EQUIPMENTNO_NEW").Value = row("EQUIPMENTNO")
PC("UPDATETIME_NEW").Value = row("UPDATETIME")
Console.WriteLine(cmd.CommandText)
Return cmd.ExecuteNonQuery()
End Function

When I run this I get an error at the line Return cmd.ExecuteNonQuery() .
It could be a syntax error but when I console.writeline cmd.Commandtext it gives me the text like
INSERT INTO MCS_CURRENT ( COMPANYCODE , EMPLOYEENO , COSTCENTER , LEADID , WORKSCHEDULEDATE , JOBNO , OPERATION , LINEHOURS , DATEIN, TIMEIN , DATEOUT , TIMEOUT ,ETCIN , ETCOUT , EQUIPMENTNO , UPDATETIME ) VALUES ( ? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

and not with the actual values of ? s so that I can run it on the sql query analyser and debug.
How do I get the commndtext with all the passed parameters values.I do see all the parametercollection getting populated in my submitInsert function above. But it doesnt show up in the command text.
Please help
thx

[Shaggy Hiker]

I have always found this type of debugging to be the most painful thing to do. As you know, the problem is somewhere in the SQL string, but once the string gets to a certain size, it is REALLY tough to parse out as a single string.

While I don't have an answer to your specific question about seeing the string with all the parameters spelled out (though I'd like to see an answer to that), I do have a couple of suggestions.

1) Step through the code that puts the info into the Query. If you highlight the part to the right of the equals sign and press Shift+F9 (or is it control....), you will see what is being passed in.

2) This would be tough for your code, but you might try removing pieces of the query to figure out which piece is causing the error.

[campster]

PC.Add("COMPANYCODE_NEW", SqlDbType.Int)

Does this make a difference?

PC.Add("[B]@[/B]COMPANYCODE_NEW", SqlDbType.Int)

[campster]

After a little reading found that @ is required in the SQL string when using SQL Server. Try something like:

strsql = "INSERT INTO MCS_CURRENT ( COMPANYCODE ,
EMPLOYEENO , COSTCENTER , LEADID , WORKSCHEDULEDATE , JOBNO , 
OPERATION , LINEHOURS , DATEIN, TIMEIN , DATEOUT , TIMEOUT ,ETCIN ,
ETCOUT , EQUIPMENTNO , UPDATETIME ) VALUES ( 
@COMPANYCODE_NEW ,@Param2,etc) "
Dim CMD As New SqlCommand(strsql, MyConnection)
Dim PC As SqlParameterCollection = CMD.Parameters
PC.Add("@COMPANYCODE_NEW", SqlDbType.Int)
...

[sdeepak]

Thank you Campster . That fixed it!