Error inserting record into SQL Server using ADO .Net

  • Thread starter Thread starter Roopali via DotNetMonster.com
  • Start date Start date
R

Roopali via DotNetMonster.com

Please tell me what could be the problem with this piece of code

Error : Message "Prepared statement '(@ID int,@fname varchar(50),@lname
varchar(50),@mname varchar(50' expects parameter @ID, which was not
supplied."


Dim ConnString As String
ConnString = ConfigurationSettings.AppSettings("conn")
oConn = New SqlConnection(ConnString)
oConn.Open()

'Create insertion string with values as parameters
Dim insertString As String
insertString = "Insert into EmployeeInfo
EmpID,FirstName,MiddleName,LastName,Gender,Designation,AddressLine1,AddressLine2,AddressLine3,City,State,Country,PinCode,RPhoneNumber,MobileNumber,Active)
values
(@ID,@fname,@mname,@lname,@gender,@design,@add1,@add2,@add3,@city,@state,@country,@pincode,@rphone,@mphone,@active)"

'Create the command obj and fill the parameters with data from the from
Dim sqlcmd As SqlCommand
sqlcmd = New SqlCommand(insertString, oConn)

With sqlcmd.Parameters
..Add(New SqlParameter("@ID", SqlDbType.Int, 9, 1))
..Add(New SqlParameter("@fname", SqlDbType.VarChar, 50,TxtFirstName.Text))
..Add(New SqlParameter("@lname", SqlDbType.VarChar, 50, TxtLastName.Text))
..Add(New SqlParameter("@mname", SqlDbType.VarChar, 50, TxtMiddleName.Text))
..Add(New SqlParameter("@gender", SqlDbType.Char, 1,
RadioButtonList1.SelectedItem.Value))
..Add(New SqlParameter("@design", SqlDbType.VarChar, 50, txtDesignation.Text)
)
..Add(New SqlParameter("@add1", SqlDbType.VarChar, 50, TxtAddress1.Text))
..Add(New SqlParameter("@add2", SqlDbType.VarChar, 50, TxtAddress2.Text))
..Add(New SqlParameter("@add3", SqlDbType.VarChar, 50, TxtAddress3.Text))
..Add(New SqlParameter("@city", SqlDbType.VarChar, 50, TxtCity.Text))
..Add(New SqlParameter("@state", SqlDbType.VarChar, 50, TxtState.Text))
..Add(New SqlParameter("@country", SqlDbType.VarChar, 50, txtCountry.Text))
..Add(New SqlParameter("@pincode", SqlDbType.VarChar, 50, TxtPincode.Text))
..Add(New SqlParameter("@rphone", SqlDbType.VarChar, 50, TxtResidenceNo.Text)
)
..Add(New SqlParameter("@mphone", SqlDbType.VarChar, 50, TxtMobileNo.Text))
..Add(New SqlParameter("@active", SqlDbType.Char, 1, "A"))

End With

'sqlcmd.Connection.Open()
sqlcmd.ExecuteNonQuery() ' I am getting a problem here
Response.Write("<b>Record Added</b><br>" + insertString)
sqlcmd.Connection.Close()
 
Please tell me what could be the problem with this piece of code
Error : Message "Prepared statement '(@ID int,@fname varchar(50),@lname
varchar(50),@mname varchar(50' expects parameter @ID, which was not
supplied."
Click to expand...
You don't seem to be filling any VALUES into the parameters before
calling the .ExecuteNonQuery method. You don't only have to DEFINE the
parameters, you also have to set values to them!

MArc
================================================================
Marc Scheuner May The Source Be With You!
Berne, Switzerland m.scheuner -at- inova.ch
 
By any chance is EmpID an identity field?

If so, you can't insert it without set identity_insert on.

Try removing ID, @ID and the @ID paramater from your code
 
Back
Top