Question System.Data.SqlClient.SqlException: Incorrect syntax near

magedassad · Aug 28, 2011

Hello, I'm not sure where my mistake is but I'm getting SqlException was unhandled by user code.

system.Data.SqlClient.SqlException: Incorrect syntax near (textbox values) on the sQuery.ExecuteNonQuery()

[XCODE]
<%@ Import Namespace="System.Data" %>

<%@ Import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server" id="Data Source=Database at godaddy; Initial Catalog=Database name; User ID=****; ">

Protected Sub btnRegister_Click(ByVal sender As Object, ByVal e As System.EventArgs)

Dim fname As String = txtFname.Text
Dim lname As String = txtLname.Text
Dim grade As String = ddlGrade.SelectedValue
Dim phone As String = txtPhone.Text
Dim email As String = txtEmail.Text
Dim dob As String = txtDob.Text
Dim Emergency As String = txtEmergency.Text

Dim myconnection As New SqlConnection("Data Source=*****; Initial Catalog=****; User ID=****; Password=****;")
myconnection.Open()

Dim sQuery As New SqlClient.SqlCommand("INSERT INTO WinterRetreat2011(fname,lname, grade,phone,email, [Date of Birth],[emergency num]) VALUES('" & fname & "," & lname & "," & grade & "," & phone & "," & email & "," & dob & "," & Emergency & "'", myconnection)
sQuery.Connection = myconnection
sQuery.ExecuteNonQuery()

lblComplete.Text = "Registration has been completed successfully!!!"
lblComplete.Visible = True

txtFname.Text = ""
txtLname.Text = ""
ddlGrade.SelectedIndex = 0
txtPhone.Text = ""
txtEmail.Text = ""
txtDob.Text = ""
txtEmergency.Text = ""

End Sub
</script>
[/XCODE]

Any help is appreciated,
thanks.

jmcilhinney · Aug 28, 2011

Don't use string concatenation to insert variables into SQL code. Always use parameters. To find out why and how, follow the Blog link in my signature and check out my post on ADO.NET parameters. Once you modify the code accordingly, you should find that your issue goes away.

magedassad · Aug 29, 2011

Thanks for your reply

Thanks for the reply.

I've tried the following but I'm getting new error. {"Procedure Winter2011 has no parameters and arguments were supplied."}

Try
Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("Winter2011", myconnection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.AddWithValue("@fname", fname)
command.Parameters.AddWithValue("@lname", lname)
command.Parameters.AddWithValue("@grade", grade)
command.Parameters.AddWithValue("@phone", phone)
command.Parameters.AddWithValue("@email", email)
command.Parameters.AddWithValue("@[Date of Birth]", dob)
command.Parameters.AddWithValue("@[Emergency Num]", Emergency)
command.ExecuteNonQuery()
Finally
myconnection.Close()
End Try

USE [Database]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Winter2011]
AS

DECLARE @fname varchar(50)
DECLARE @lname varchar(50)
DECLARE @phone varchar(50)
DECLARE @email varchar(50)
DECLARE @dob varchar(50)
DECLARE @grade varchar(50)
DECLARE @emergency varchar(50)

INSERT INTO [Database].[dbo].[WinterRetreat2011] ([First Name],[Last Name], grade,phone,email, [Date of Birth],[Emergency num]) values (@fname , @lname , @grade, @phone, @email, @dob, @emergency)

Do you see anything wrong with the sql stored procedure?
Thanks again.

jmcilhinney · Aug 29, 2011

You are declaring local variables in your sproc, not parameters.

On another note, you shouldn't just use varchar for everything. Your 'Date of Birth' column at least should not be text. Use the correct data type for the type of the data.

Question System.Data.SqlClient.SqlException: Incorrect syntax near

magedassad

New member

jmcilhinney

VB.NET Forum Moderator

magedassad

New member

jmcilhinney

VB.NET Forum Moderator

Similar threads

Share this page

Latest posts