[RESOLVED] Vb.net 2008 sqlcmd.parameter

Hi Good Guys :wave:
I need your help. Please help me.

I am trying to create INSERT SQL using WHERE syntaxing in order to ensure no duplication with the SQLCMD.PARAMETER but the WHERE clause generate this error message:

Incorrect syntax near the keyword 'Where'.

----------------------------------------
Here are the coding:

Code:

---

Dim strSql As String
      strSql = "Insert into tblCustomers "
      strSql &= "(CustomerID, ContactName,Address,City,PostalCode,Country)"
      strSql &= " Values (@CustIdValue, @ContNameValue, @CustAddrValue, @CityValue, @PostCodevalue, @CountryValue)"
    strSql &= " Where CustomerID <> @CustIDValue "    <--- error 

    sqlconn = New SqlConnection(connstr)
    sqlconn.Open()

    sqlcmd = New SqlCommand(strSql, sqlconn)
    With sqlcmd.Parameters
      .AddWithValue("@CustIDValue", CType(Me.txtCustID.Text, String))
      .AddWithValue("@ContNameValue", CType(Me.txtContName.Text, String))
      .AddWithValue("@CustAddrValue", CType(Me.txtCustAddr.Text, String))
      .AddWithValue("@CityValue", CType(Me.txtCity.Text, String))
      .AddWithValue("@PostCodeValue", CType(Me.txtPostCode.Text, String))
      .AddWithValue("@CountryValue", CType(Me.txtCountry.Text, String))
  End With

---

Is your CustID is of Integer datatype , if so then you must add a parameter of type Integer

does that sql statement make sense logically?

seems like you would need to do a SELECT COUNT(*) for that customerid first and if the returned value is 0, then INSERT, if it is > 0 (meaning customer exists), then do an UPDATE command.

Quote:

---

Originally Posted by aashish_9601

Is your CustID is of Integer datatype , if so then you must add a parameter of type Integer

---

The CustomerID whose DataType is nvarchar that's why I used this statement
.AddWithValue("@CustIDValue", CType(Me.txtCustID.Text, String))

Quote:

---

Originally Posted by stateofidleness

does that sql statement make sense logically?

seems like you would need to do a SELECT COUNT(*) for that customerid first and if the returned value is 0, then INSERT, if it is > 0 (meaning customer exists), then do an UPDATE command.

---

In VB6 I will use Select Count(*) but in VB.NET 2008 I would use this coding logic to determine record existence.

Code:

---

Dim intReturn as integer
Dim strSql as string = "Select * from tblCustomer where CustomerID = @custIDvalue.

sqlcmd = new sqlcommand(strSql, sqlconn)
With sqlcmd.parameters
  .addwithvalue("@custIDValue", ctype(txtCustId.text,string)
End with

intReturn = sqlcmd.ExecuteScalar

---

Hi stateofIdleness and aashish,

Yaa......Hoooo.......!!!!

Thanks to both of you for sharing your knowledge with me. I have tried out both of your suggestions and my codings are now working very well.

Both of you are awesome and generous in sharing information and knowledge with me. I am so glad to meet both of you at ths WONDERFUL FORUM> and I appreciate this very much. Both of you are Wonderful.