DATETIME conversion problem in stored procedure

Hi,

I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .

Stored procedure:

ALTER PROCEDURE dbo.SPTest
@.testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @.testvalue, 'EUR/USD')
RETURN 1

VB.NET code:

Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))

When I run this the ReturnVal is 0.

I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.

hi,

dazfl wrote:

Hi,

I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .

Stored procedure:

ALTER PROCEDURE dbo.SPTest
@.testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @.testvalue, 'EUR/USD')
RETURN 1

usually return values other than 0 (zero) indicate a procedure error.. so, 1 is usually read as error and not "success"..

VB.NET code:

Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))

When I run this the ReturnVal is 0.

I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.

try directly consuming a command and relative parameters, like

Dim cmd As New SqlClient.SqlCommand

With cmd

.CommandText = "schema.procedureName"

.CommandType = CommandType.StoredProcedure

.CommandTimeout = n

.Connection = connection

Dim p As New SqlClient.SqlParameter

With p

.ParameterName = "@.testvalue"

.SqlDbType = SqlDbType.DateTime

.Value = DateTime.Now

.Direction = ParameterDirection.Input

End With

.Parameters.Add(p)

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

so that you can check (1st important addition of the command and parameters behaviour) and validate parameters initialization... more.. the parameter automatically handles this kind of conversions..

regards