Sql syntax error..Please help..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Sql syntax error..Please help..

Author

Topic

SQLNEWBIZ
Starting Member

27 Posts

Posted - 2012-12-26 : 07:55:33

Hello,all, here is my code.. I want to insert the textbox values ,both string and integer,direct to my sql DB itemlistingDB by hitting save button.this is the code i have written.
when the code is build,, its showing "Incorrect syntax near ','." when save button is pressed .
someone please help me,its urgent..

Private Sub saveFunc()

'
Dim con As New SqlConnection("Data Source=V-PC\WINCCPLUSMIG;Initial Catalog=ProductDB;Integrated Security=True")
'"Insert into Dealer" + "values('" + D_B1.Text + "'," + D_B2.Text + "'," + D_B3.Text + "'," + D_B4.Text + "'," + D_B5.Text + "'," + D_B6.Text + ")", connection_a);

con.Open()

Dim cmd As New SqlCommand
cmd.CommandText = "INSERT INTO ItemListingDB(ItemCode,ItemName,Cost,Price) Values('" & txtitemcode.Text & "','" & txtItemName.Text & "'," & txtcost.Text & "," & txtprice.Text & ")"

cmd.Connection = con

cmd.ExecuteNonQuery()

con.Close()
End Sub

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-26 : 08:16:38

May be this for Dealer Table...... try once

"Insert into Dealer" + "values('" + D_B1.Text + "','" + D_B2.Text + "','" + D_B3.Text + "','" + D_B4.Text + "','" + D_B5.Text + "','" + D_B6.Text + "')",

--
Chandu

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-26 : 08:24:02

In addition to fixing the syntax as Chandu suggested, also note that you have two strings that insert into Dealer table and ItemListingDB. The way you are doing this, it would insert only into ItemListingDB, even if the syntax was fixed

Using ExecuteNonQuery is very simple - you need a command object. The command object needs two things a connection string, and a command text. If you take a look at the example on this page and try to adapt that to your requirement, that may be easier: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

SQLNEWBIZ
Starting Member

27 Posts

Posted - 2012-12-26 : 08:32:21

sorry for the confusion,"Insert Into Dealer is not the part of my code,i mean the insert query below is what i want.all the time the code is built,the same syntax error is returing.

quote:
Originally posted by bandi

May be this for Dealer Table...... try once

"Insert into Dealer" + "values('" + D_B1.Text + "','" + D_B2.Text + "','" + D_B3.Text + "','" + D_B4.Text + "','" + D_B5.Text + "','" + D_B6.Text + "')",

--
Chandu

SQLNEWBIZ
Starting Member

27 Posts

Posted - 2012-12-26 : 08:55:51

i cant find any change to be done on the below query.so while building,{"Incorrect syntax near ','." is showing.plz help someone. its urgent
cmd.CommandText = "INSERT INTO ItemListingDB(ItemCode,ItemName,Cost,Price) Values('" & Me.txtitemcode.Text & "','" & Me.txtItemName.Text & "'," & Me.txtcost.Text & "," & Me.txtprice.Text & ")"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-26 : 09:11:40

I think you may just be missing a space. From what you've written, your code will look like this
Insert Into Dealervalues('Text' etc.
Try adding a space
"Insert Into Dealer " + values(

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-26 : 09:13:56

You have a few missing single quotes - see below. I broke it up into multiple lines for readability, but you don't have to.

An easy way to debug this is to print out the command text in the debugger after this statement and look at it (or post it, or run it in SQL Server Management Studio)

cmd.CommandText = "INSERT INTO ItemListingDB(ItemCode,ItemName,Cost,Price) Values('" _
	& txtitemcode.Text  _
	& "','"  _
	& txtItemName.Text  _
	& "','"  _
	& txtcost.Text  _
	& "','"  _
	& txtprice.Text & "')"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-26 : 13:11:19

One thing I forgot to mention:

If the values in the text boxes can have single quotes in them, you would need to replace single quotes with two single quotes for each occurrence. So something like shown below. That may be the issue you are running into. Be sure to do that for each text box that potentially can have single quotes in the data entered in it:

....
	& "','"  _
	& txtItemName.Text.Replace("'", "''")  _
	& "','"  _
...

SQLNEWBIZ
Starting Member

27 Posts

Posted - 2012-12-27 : 02:18:05

Than you,for your help..I resolved the problem by addwithvalues without directly passing textbox data in the query..

FredBailey
Starting Member

1 Post

Posted - 2013-01-19 : 04:31:57

I think you should write the insert command again.There is problem of space or double quotes.Check it
unspammed

bluffy
Starting Member

2 Posts

Posted - 2013-06-06 : 01:48:44

how to use pivot table in mysql

Regards,
Bluffy,
Currency Trading in India: UFXMARKETS

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-06 : 02:20:03

Hi bluffy,
This is SQL Server (MSSQL) forum...

Follow relevant database forums like http://forums.mysql.com/
Any way use the below links for Cross tabulation / PIVOT Tables in MySQL
http://www.codeproject.com/Articles/363339/Cross-Tabulation-Pivot-Tables-with-MySQL
http://buysql.com/mysql/12-how-to-pivot-tables.html

--
Chandu

cursor system
Starting Member

2 Posts

Posted - 2013-06-11 : 05:15:42

The best way to debug this type of problem is by using one field one value. That is
cmd.CommandText = "INSERT INTO ItemListingDB(ItemCode) Values('" & txtitemcode.Text & "')"

After this is tested and okay, then

cmd.CommandText = "INSERT INTO ItemListingDB(ItemCode,ItemName) Values('" & txtitemcode.Text & "','" & txtItemName.Text & "')"

and so on untill all are done. With thisb you will easily know the one that is making the error.

Get helpful tips on technology @ unspammed

newwaysys
Starting Member

9 Posts

Posted - 2015-04-09 : 04:20:55

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources