473,385 Members | 2,028 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 software developers and data experts.

INSERT statement using variables

sm
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

Nov 21 '05 #1
11 16177
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.

"sm" <sm@discussions.microsoft.com> wrote in message
news:A9**********************************@microsof t.com...
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
Nov 21 '05 #2
sm

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" wrote:
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.

"sm" <sm@discussions.microsoft.com> wrote in message
news:A9**********************************@microsof t.com...
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

Nov 21 '05 #3
sm
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" wrote:
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.

"sm" <sm@discussions.microsoft.com> wrote in message
news:A9**********************************@microsof t.com...
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

Nov 21 '05 #4
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.
"sm" <sm@discussions.microsoft.com> wrote in message
news:30**********************************@microsof t.com...

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" wrote:
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.

"sm" <sm@discussions.microsoft.com> wrote in message
news:A9**********************************@microsof t.com...
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

Nov 21 '05 #5
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.

"sm" <sm@discussions.microsoft.com> wrote in message
news:28**********************************@microsof t.com...
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" wrote:
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.

"sm" <sm@discussions.microsoft.com> wrote in message
news:A9**********************************@microsof t.com...
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

Nov 21 '05 #6
sm
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" wrote:
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.
"sm" <sm@discussions.microsoft.com> wrote in message
news:30**********************************@microsof t.com...

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" wrote:
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.

"sm" <sm@discussions.microsoft.com> wrote in message
news:A9**********************************@microsof t.com...
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


Nov 21 '05 #7
Pass the date string as is without doing the conversion. But, include it
inside single quotes. I think it should work.

"sm" <sm@discussions.microsoft.com> wrote in message
news:6C**********************************@microsof t.com...
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" wrote:
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.
"sm" <sm@discussions.microsoft.com> wrote in message
news:30**********************************@microsof t.com...

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" wrote:
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.

"sm" <sm@discussions.microsoft.com> wrote in message
news:A9**********************************@microsof t.com...
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


Nov 21 '05 #8
"sm" <sm@discussions.microsoft.com> schrieb:
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>

--
Herfried K. Wagner [MVP]
<URL:http://dotnet.mvps.org/>

Nov 21 '05 #9
sm
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" wrote:
Pass the date string as is without doing the conversion. But, include it
inside single quotes. I think it should work.

"sm" <sm@discussions.microsoft.com> wrote in message
news:6C**********************************@microsof t.com...
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" wrote:
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.
"sm" <sm@discussions.microsoft.com> wrote in message
news:30**********************************@microsof t.com...

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" wrote:
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.

"sm" <sm@discussions.microsoft.com> wrote in message
news:A9**********************************@microsof t.com...
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



Nov 21 '05 #10
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.

"sm" <sm@discussions.microsoft.com> wrote in message
news:D7**********************************@microsof t.com...
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" wrote:
Pass the date string as is without doing the conversion. But, include it
inside single quotes. I think it should work.

"sm" <sm@discussions.microsoft.com> wrote in message
news:6C**********************************@microsof t.com...
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" wrote:
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.
"sm" <sm@discussions.microsoft.com> wrote in message
news:30**********************************@microsof t.com...

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" wrote:
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.

"sm" <sm@discussions.microsoft.com> wrote in message
news:A9**********************************@microsof t.com...
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



Nov 21 '05 #11
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;Integrate d 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
///
"sm" <sm@discussions.microsoft.com>
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

Nov 21 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: newbie_mw | last post by:
Seems my post was buried in more cries for help :-) I will try again. It's probably a very novice question so please take a look! Thanks!...
0
by: Ted Greek | last post by:
How do I get two values for two different fields in an insert statement values clause using one select? For instance: declare @test int @test gets assigned... insert into (id, field1,...
2
by: mvr | last post by:
Hi all I are using IIS 5.0, Oracle 8.1. I am having problem with the following Insert Statement when used on Production Web server with SSL(https://...., Verisign). This doesn't occur...
6
by: Kathy Burke | last post by:
Ugh. I'm using the following in an asp.net. I get an Syntax Error in INSERT INTO Statement on line Cmd1.ExecuteNonQuery(). I've made all my database fields text (just to eliminate that as a...
7
by: Cindy H | last post by:
Hi I'm having a problem getting the insert statement correct for an Access table I'm using. The Access table uses an autonumber for the primary key. I have tried this: INSERT INTO Tournaments...
11
by: themoonisdown09 | last post by:
I am using Visual Studio 2003, C# .NET I can't get this INSERT statement to work. I can get an UPDATE to work, but not this INSERT. Here is my code... ****************** //opens database...
2
by: btuisee | last post by:
I'm having an issue getting a variable to pass through to an oracle database. I have an insert statment and one of the values are a variable called q1. Q1 is a variable that gets it's value from a...
1
by: bcap | last post by:
Is it possible to create a SQL In statement using variables? For example, here is what can be done: SELECT Test.ID, Test.Desc FROM Test WHERE (((Test.ID) In (3,6,9,12))); But, I would...
2
by: jayjayplane | last post by:
I want to insert value into one temp table, but based on the value from another table, like: select s2_sess_datetime,s2_individual_session from lop_2008_staging if s2_sess_datetime is not null...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.