[RESOLVED] Vb.net 2008 sqlcmd.parameter

[Lennie]

Hi Good Guys
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:

 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

[aashish_9601]

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

[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.

[Lennie]

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))

[Lennie]

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.

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

[Lennie]

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.