INSERT statement using variables

[Guest]

Hi All,

Can anybody give me the syntax to insert a record into SQL server through VB
code using variables?

The following statement is failing!
sInsertQuery = "INSERT INTO TestTab (Col1, Col2, Col3, Col4) Values(" & _
str1 & "," & dt1 & "," & str2 & "," & str3 & ")"

where str1 = "col1"
dt1 = getdate()
str2 = "col2"
str3 = "col3"

Thanks

 

[Shiva]

Hi,
Enclose all string values in single quotes:

"INSERT INTO TestTab (Col1, col2, col3, col4) VALUES ('" & str1 & "','" &
dt1 & "','" & str2 & "','" & str3 & "')"

If string values contain inturn contain single quote escape them with two
consequtive single quotes.

HTH.

Hi All,

Can anybody give me the syntax to insert a record into SQL server through VB
code using variables?

The following statement is failing!
sInsertQuery = "INSERT INTO TestTab (Col1, Col2, Col3, Col4) Values(" & _
str1 & "," & dt1 & "," & str2 & "," & str3 & ")"

where str1 = "col1"
dt1 = getdate()
str2 = "col2"
str3 = "col3"

Thanks

 

[Guest]

Thanks Shiva.

I have added quotes to the string values. As follows:
sInsertQuery = "INSERT INTO TestTab (Col1, Col2, Col3, Col4) Values(" & _
"'str1' , dt1 , 'str2' , 'str3')"
Now I get an error at dt1

 

[Guest]

I have a question though, won't SQL server consider the string variable
itself as the
value, if the value is enclosed in quotes?

 

[Shiva]

Hi,
What is the value passed for dt1? Is it in appropriate format? Try this
format: YYYY-MM-DD HH:mm:SS, enclosed in single quotes.



Thanks Shiva.

I have added quotes to the string values. As follows:
sInsertQuery = "INSERT INTO TestTab (Col1, Col2, Col3, Col4) Values(" & _
"'str1' , dt1 , 'str2' , 'str3')"
Now I get an error at dt1

 

[Shiva]

You are not enclosing the variable itself, but just its value. If for
example s1 = "abc", then the expression "'" & s1 & "'" will produce 'abc'

I hope I got your question right.

I have a question though, won't SQL server consider the string variable
itself as the
value, if the value is enclosed in quotes?

 

[Guest]

Hi,

With respect to the date, I have a string
str ="2004/04/04 15:52"
which I am converting to date using CDate(str) and passing str to the INSERT
statement. Where do I specify the format YYYY-MM-DD HH:mm:SS?

Thanks

 

[Shiva]

Pass the date string as is without doing the conversion. But, include it
inside single quotes. I think it should work.

Hi,

With respect to the date, I have a string
str ="2004/04/04 15:52"
which I am converting to date using CDate(str) and passing str to the INSERT
statement. Where do I specify the format YYYY-MM-DD HH:mm:SS?

Thanks

 

[Herfried K. Wagner [MVP]]

Can anybody give me the syntax to insert a record into
SQL server through VB code using variables?

The following statement is failing!
sInsertQuery = "INSERT INTO TestTab (Col1, Col2, Col3, Col4) Values(" & _
str1 & "," & dt1 & "," & str2 & "," & str3 & ")"

Use an 'InsertCommand' + parameters instead:

Using Parameters with a 'DataAdapter'
<URL:http://msdn.microsoft.com/library/en-us/cpguide/html/cpconusingparameterswithdataadapters.asp>

 

[Guest]

Thanks Shiva; the insert is successful now.

But the table shows 3:52 PM instead of 15:52! Is there some way to get the
datetime in the table in this format "2004/04/04 15:52"?

Thanks once again.

 

[Shiva]

It is just the formatting (current regional setttings) that it uses to
display the value. Have a look at CAST and CONVERT operators to customize
the output values.

Thanks Shiva; the insert is successful now.

But the table shows 3:52 PM instead of 15:52! Is there some way to get the
datetime in the table in this format "2004/04/04 15:52"?

Thanks once again.

 

[Cor Ligthert]

SM,

I sand this sample that I made some days ago to this newsgroup, I have now
changed it a little bit in this message to make it more globalized usable.

You see in this used the parameters.

I hope this helps?

Cor

\\\You can use for this sample the Northwind database in SQL
Public Class Main
Public Shared Sub Main()
Dim Conn As New SqlClient.SqlConnection _
("Server=localhost;DataBase=Northwind;Integrated Security=SSPI")
Try
Dim strSQL As String = "INSERT INTO Employees " & _
"(LastName, FirstName, HireDate)" & _
"VALUES ('Kevin', 'Hodgson', @HireDate)"
Dim cmd As New SqlClient.SqlCommand(strSQL, Conn)
Dim myparam As New SqlClient.SqlParameter
myparam.DbType = DbType.DateTime
myparam.ParameterName = "@HireDate"
myparam.Value = New Date(2004, 11, 2)
cmd.Parameters.Add(myparam)
Conn.Open()
cmd.ExecuteNonQuery()
Catch ex As SqlClient.SqlException
MessageBox.Show(Ex.ToString)
Catch ex As Exception
MessageBox.Show(Ex.ToString)
End Try
End Sub
End Class
///