Problems writing SQL
I've been writing in VB.Net for a couple of months now and have used SQL commands many times in my code, but I am having trouble writing to one specific table in my database. I believe the problem is that I have a numeric column that I am trying to write (I came to this conclusion because it is the only one that I do not use very often) and my code keeps popping up with an exception: More information : The SqlCommand.Prepare method requires all variable length parameters to be explicitly set to a non-zero size. with code:
Dim cn As System.Data.SqlClient.SqlConnection
Dim command As System.Data.SqlClient.SqlCommand
Dim VL1 As System.Data.SqlClient.SqlParameter
Dim VL2 As System.Data.SqlClient.SqlParameter
Dim VL3 As System.Data.SqlClient.SqlParameter
Dim VL4 As System.Data.SqlClient.SqlParameter
Dim commandText As String
cn = New System.Data.SqlClient.SqlConnection(ConnectStr)
cn.Open()
commandText = "INSERT INTO [dbo].[HourMeterLog]" _
+ "([MachineName],[TotalHours],[HLRD],[DateTime])" _
+ "VALUES (@VL1,@VL2,@VL3,@VL4)"
command = New System.Data.SqlClient.SqlCommand(commandText, cn)
VL1 = command.Parameters.Add("@VL1", System.Data.SqlDbType.NVarChar, 25)
VL2 = command.Parameters.Add("@VL2", System.Data.SqlDbType.Float)
VL3 = command.Parameters.Add("@VL3", System.Data.SqlDbType.Int, 10)
VL4 = command.Parameters.Add("@VL4", System.Data.SqlDbType.DateTime2, 0)
command.Prepare()
VL1.Value = "MachineName"
VL2.Value = 0
VL3.Value = 735562
VL4.Value = Now()
command.ExecuteNonQuery()
cn.Close()
I know my code is connecting because I have at least 15 other requests that work using this format. I would rather not use float to write to a numeric field, but this was the first time I worked with it when running this code (more than once since).
source to share
According to MSDN, the maximum character length DateTime2
is 27. So I set the length DateTime2
to 27 and it works.
VL4 = command.Parameters.Add("@VL4", System.Data.SqlDbType.DateTime2, 27)
And my working code:
Dim cn As System.Data.SqlClient.SqlConnection
Dim command As System.Data.SqlClient.SqlCommand
Dim VL1 As System.Data.SqlClient.SqlParameter
Dim VL2 As System.Data.SqlClient.SqlParameter
Dim VL3 As System.Data.SqlClient.SqlParameter
Dim VL4 As System.Data.SqlClient.SqlParameter
Dim commandText As String
cn = New System.Data.SqlClient.SqlConnection(connectionString)
cn.Open()
commandText = "INSERT INTO [dbo].[HourMeterLog]" _
+ "([MachineName],[TotalHours],[HLRD],[DateTime])" _
+ "VALUES (@VL1,@VL2,@VL3,@VL4)"
command = New System.Data.SqlClient.SqlCommand(commandText, cn)
VL1 = command.Parameters.Add("@VL1", System.Data.SqlDbType.NVarChar, 25)
VL2 = command.Parameters.Add("@VL2", System.Data.SqlDbType.Float)
VL3 = command.Parameters.Add("@VL3", System.Data.SqlDbType.Int, 10)
VL4 = command.Parameters.Add("@VL4", System.Data.SqlDbType.DateTime2, 27)
command.Prepare()
VL1.Value = "MachineName"
VL2.Value = 0
VL3.Value = 735562
VL4.Value = Now()
command.ExecuteNonQuery()
cn.Close()
source to share
For an error message knowing nothing about your table ...
"The SqlCommand.Prepare method requires all variable length parameters to be explicitly set to a non-zero size "
From MSDN ( https://msdn.microsoft.com/en-us/library/40959t6x(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1 )
'Declaration
Public Function Add ( _
parameterName As String, _
sqlDbType As SqlDbType, _
size As Integer _
) As SqlParameter
Perhaps your problems arise from:
VL4 = command.Parameters.Add("@VL4", System.Data.SqlDbType.DateTime2, 0)
Change it to
VL4 = command.Parameters.Add("@VL4", System.Data.SqlDbType.DateTime2)
source to share
Ok, so I think I wrote the AddWithValue method wrong when I tried it. I got the command to write with:
Dim cn As System.Data.SqlClient.SqlConnection
Dim command As System.Data.SqlClient.SqlCommand
Dim VL1 As System.Data.SqlClient.SqlParameter
Dim VL2 As System.Data.SqlClient.SqlParameter
Dim VL3 As System.Data.SqlClient.SqlParameter
Dim VL4 As System.Data.SqlClient.SqlParameter
Dim commandText As String
cn = New System.Data.SqlClient.SqlConnection(ConnectStr)
cn.Open()
commandText = "INSERT INTO [dbo].[HourMeterLog]" _
+ "([MachineName],[TotalHours],[HLRD],[DateTime])" _
+ "VALUES (@VL1,@VL2,@VL3,@VL4)"
command = New System.Data.SqlClient.SqlCommand(commandText, cn)
VL1 = command.Parameters.AddWithValue("@VL1", "MachineName")
VL2 = command.Parameters.AddWithValue("@VL2", 0)
VL3 = command.Parameters.AddWithValue("@VL3", 735562)
VL4 = command.Parameters.AddWithValue("@VL4", Now())
'VL1 = command.Parameters.Add("@VL1", System.Data.SqlDbType.NVarChar, 25)
'VL2 = command.Parameters.Add("@VL2", System.Data.SqlDbType.Float)
'VL3 = command.Parameters.Add("@VL3", System.Data.SqlDbType.Int)
'VL4 = command.Parameters.Add("@VL4", System.Data.SqlDbType.DateTime2)
'command.Prepare()
'VL1.Value = "MachineName"
'VL2.Value = 0
'VL3.Value = 735562
'VL4.Value = Now()
command.ExecuteNonQuery()
cn.Close()
I would have preferred to use the format I used before, but I'm glad that I'm at least writing now. Thanks for the help guys! If anyone has any ideas on how to improve my original format so that it writes correctly, let me know. and for those who wanted to get a little information in the table, I also write the field that Float wrote - this is a numeric (18,1) field. Besides this column, others can be extrapolated from the code.
source to share