Hello, I'm having problems filling a data grid from a stored procedure and could really use some help. I have not been able to figure this out.
When I call my sub loadgrid() the catch throws me this exception:
System.Data.SqlClient.SqlException: Syntax error converting datetime from character string. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at BBNSintranet.monthlyBatch.loadgrid() in c:\inetpub\wwwroot\BBNSintranet\levelOne\monthlyBatch.aspx.vb:line 132
Which is driving me crazy because I do not do anything with datetime!
Here is the SPROC:
CREATE PROCEDURE [webuser].[stateMoBatchsel]
@bachnumb char (15)
AS
truncate table test.dbo.monthlybatch
INSERT INTO test.dbo.monthlybatch (docdate, bachnumb, ws_job_number, docnumbr,userdef2, estimated_amt_units, estimated_units,User_Define_4a, User_Define_1)
SELECT
RM10301.BACHNUMB ,
RM10301.DOCDATE,
cstb_JCSOPInvoiceDetails.WS_Job_Number,
cstb_JCSOPInvoiceDetails.DOCNUMBR,
cstb_JCSOPInvoiceDetails.USERDEF2,
cstb_JCSOPInvoiceDetails.Estimated_Amt_Units,
cstb_JCSOPInvoiceDetails.Estimated_Units,
SV00300.User_Define_4a,
JC00102.User_Define_1
FROM
((test.dbo.RM10301 RM10301 RIGHT OUTER JOIN test.dbo.cstb_JCSOPInvoiceDetails cstb_JCSOPInvoiceDetails ON
RM10301.DOCNUMBR = cstb_JCSOPInvoiceDetails.DOCNUMBR)
LEFT OUTER JOIN test.dbo.SV00300 SV00300 ON
cstb_JCSOPInvoiceDetails.WS_Job_Number = SV00300.Service_Call_ID)
LEFT OUTER JOIN test.dbo.JC00102 JC00102 ON
cstb_JCSOPInvoiceDetails.WS_Job_Number = JC00102.WS_Job_Number
WHERE
LTRIM(RTRIM(RM10301.BACHNUMB)) = @bachnumb
select * from test.dbo.monthlybatch
GO
Here is the sub:
PrivateSub loadgrid()
Dim myCommand As SqlCommand
Dim myParam As SqlParameter
myCommand = New SqlCommand
myCommand.Connection = con
myCommand.CommandText = "test.webuser.stateMoBatchsel"
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@bachnumb", SqlDbType.Char, 15))
myCommand.Parameters("@bachnumb").Value = bachnumb.SelectedItem.Value
Try
If con.State = 0 Then con.Open()
dg.DataSource = myCommand.ExecuteReader()
dg.DataBind()
Catch exc As Exception
Response.Write(exc)
Finally
If con.State = ConnectionState.Open Then
con.Close()
EndIf
EndTry
EndSub
One more thing that could be important. The datatypes in the database are the same as in the SPROC and my parameters that I'm trying to pass to the SPROC.
Any help would be great!
When I call my sub loadgrid() the catch throws me this exception:
System.Data.SqlClient.SqlException: Syntax error converting datetime from character string. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at BBNSintranet.monthlyBatch.loadgrid() in c:\inetpub\wwwroot\BBNSintranet\levelOne\monthlyBatch.aspx.vb:line 132
Which is driving me crazy because I do not do anything with datetime!
Here is the SPROC:
CREATE PROCEDURE [webuser].[stateMoBatchsel]
@bachnumb char (15)
AS
truncate table test.dbo.monthlybatch
INSERT INTO test.dbo.monthlybatch (docdate, bachnumb, ws_job_number, docnumbr,userdef2, estimated_amt_units, estimated_units,User_Define_4a, User_Define_1)
SELECT
RM10301.BACHNUMB ,
RM10301.DOCDATE,
cstb_JCSOPInvoiceDetails.WS_Job_Number,
cstb_JCSOPInvoiceDetails.DOCNUMBR,
cstb_JCSOPInvoiceDetails.USERDEF2,
cstb_JCSOPInvoiceDetails.Estimated_Amt_Units,
cstb_JCSOPInvoiceDetails.Estimated_Units,
SV00300.User_Define_4a,
JC00102.User_Define_1
FROM
((test.dbo.RM10301 RM10301 RIGHT OUTER JOIN test.dbo.cstb_JCSOPInvoiceDetails cstb_JCSOPInvoiceDetails ON
RM10301.DOCNUMBR = cstb_JCSOPInvoiceDetails.DOCNUMBR)
LEFT OUTER JOIN test.dbo.SV00300 SV00300 ON
cstb_JCSOPInvoiceDetails.WS_Job_Number = SV00300.Service_Call_ID)
LEFT OUTER JOIN test.dbo.JC00102 JC00102 ON
cstb_JCSOPInvoiceDetails.WS_Job_Number = JC00102.WS_Job_Number
WHERE
LTRIM(RTRIM(RM10301.BACHNUMB)) = @bachnumb
select * from test.dbo.monthlybatch
GO
Here is the sub:
PrivateSub loadgrid()
Dim myCommand As SqlCommand
Dim myParam As SqlParameter
myCommand = New SqlCommand
myCommand.Connection = con
myCommand.CommandText = "test.webuser.stateMoBatchsel"
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("@bachnumb", SqlDbType.Char, 15))
myCommand.Parameters("@bachnumb").Value = bachnumb.SelectedItem.Value
Try
If con.State = 0 Then con.Open()
dg.DataSource = myCommand.ExecuteReader()
dg.DataBind()
Catch exc As Exception
Response.Write(exc)
Finally
If con.State = ConnectionState.Open Then
con.Close()
EndIf
EndTry
EndSub
One more thing that could be important. The datatypes in the database are the same as in the SPROC and my parameters that I'm trying to pass to the SPROC.
Any help would be great!