INSERT statement using variables

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

Subscribe Post Reply

16180

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.

"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

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

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

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

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.

"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

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

Shiva

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

Herfried K. Wagner [MVP]

"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

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

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.

"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

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;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

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!...

PHP

Insert statement question (multifield) #2

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,...

Microsoft SQL Server

Problems with insert statement using SSL(Verisign)

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...

ASP / Active Server Pages

Going nuts...a sql insert statement error...how to debug from here?

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...

ASP.NET

Access database insert statement with an autonumber (identity) in vb.net

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...

Visual Basic .NET

C# INSERT statement using OleDb

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...

.NET Framework

INSERT statement with c# variables

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...

ASP / Active Server Pages

SQL In Statement with Variables?

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...

Microsoft Access / VBA

Help on conditional insert statement using t-sql

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...

Microsoft SQL Server

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

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...

Windows Server

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

INSERT statement using variables

Similar topics