Incorrect syntax near 'nvarchar'. Must declare the scalar variable "@". In VB.NET error

Question

1.00/5 (1 vote)

See more:

i need to insert data from datagrid to sql server database. but my coding have a error."Incorrect syntax near 'nvarchar'. Must declare the scalar variable "@". in vb.error" This is my error...pls fix the problem....

And belove my coding showed..

Dim conn As New SqlConnection
Dim cmd As SqlCommand
Dim sqlstring As String
Dim paramdic As New Dictionary(Of String, Object)

conn.ConnectionString = "Data Source=ATHILINGAM\SQLEXPRESS;Initial Catalog=Attendance;Integrated Security=True;"

conn.Open()

paramdic.Add("@[Staff Id]", "value1")
paramdic.Add("@[Staff Name]", "value2")
sqlstring = "INSERT INTO dbo.Attend1(field1, field2) VALUES ([@Staff Id], @[Staff Name])"
cmd = New SqlCommand(sqlstring, conn)
For Each keyval As KeyValuePair(Of String, Object) In paramdic
cmd.Parameters.AddWithValue(keyval.Key, keyval.Value)
Next
cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Close()

what is the mistake on my coding....

What I have tried:

i need to insert data from datagrid to sql server database. but my coding have a error."Incorrect syntax near 'nvarchar'. Must declare the scalar variable "@". in vb.error" This is my error...pls fix the problem....

Posted 13-Apr-18 23:52pm

Member 13738315

Updated 14-Apr-18 1:09am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-04-14T01:10:00

Solution 1

In SQL '@' says "this is a variable name", and variable sin SQL cannot contain spaces.
Try this:

VB

paramdic.Add("@StaffId", "value1")
paramdic.Add("@StaffName", "value2")
sqlstring = "INSERT INTO dbo.Attend1(field1, field2) VALUES (@StaffId, @StaffName)"

Posted 14-Apr-18 1:10am

OriginalGriff

Updated 14-Apr-18 1:11am

v2

Comments

Member 13738315 14-Apr-18 7:15am

what is the different from my coding to your coding. it is same as my coding. I can't understand . pls tell me....

Member 13738315 14-Apr-18 7:16am

do you represent space between variable names?

OriginalGriff 14-Apr-18 7:27am

You cannot have spaces in variable names in SQL!

OriginalGriff 14-Apr-18 7:26am

To do anything in computing you must pay attention to details!
Yours:
paramdic.Add("@[Staff Id]", "value1")
Mine:
paramdic.Add("@StaffId", "value1")
Yours:
paramdic.Add("@[Staff Name]", "value2")
Mine:
paramdic.Add("@StaffName", "value2")
Yours:
sqlstring = "INSERT INTO dbo.Attend1(field1, field2) VALUES ([@Staff Id], @[Staff Name])"
Mine:
sqlstring = "INSERT INTO dbo.Attend1(field1, field2) VALUES (@StaffId, @StaffName)"

Member 13738315 14-Apr-18 7:35am

it is worked. But i mean "value1" is first column(StaffId) in the DataGridview data. I worked with Attendance System. so, 1)i need to select and display from the staff table in database. 2) with combobox, i marked present or obsent , staffName, StaffId and Present or obsent values stored in another table which is Attend1. First problem is solved. But second problem is sloved half and half. Because i represet 'Value1' is Staffid or first column of datgridview.pls guide me....

OriginalGriff 14-Apr-18 7:46am

All you have to do is replace your fixed string values with those from your data.
Come on, think about it - this is the simple stuff! :laugh:

Member 13738315 14-Apr-18 8:35am

I discoverd this command...but small error...pls see this,,

sqlstring = "INSERT INTO dbo.Attend1(StaffId, StaffName) VALUES ('" & dr.Cells(0).Value & "','" & dr.Cells(1).Value & "')"

Error: 'dr' not declared..How to declare it..

OriginalGriff 14-Apr-18 10:00am

You know, there are times when helping other out here can get quite depressing.
And when you see someone who very clearly has been told to use parameterised queries throw all that in the bin and use string concatenation instead that's one of them. Why? Because it's obvious that "thinking" isn't featuring highly in the posters "To Do" list for the day, and they are working on a development strategy of "guess and hope someone will sort it out for me later if it doesn't work."

And this is - you guessed, I'm sure - one of those times.
You've taken code at random from the internet, thrown it into your code, hit it with a big hammer a few times, and are surprised that it doesn't compile cleanly or do exactly what you want? :sigh:

Stop.
Think.
And then try writing it yourself - you won't learn any other way...

Member 13738315 14-Apr-18 10:08am

I got it...bro