dotnetsr

Dear All,

I'm using following Public Function to insert new record. the function created. . I know how to do it in the form directly without using a DAL. But with DAL and this function this is not working and there is no error msg except 'Error in Syntax' Insert into Statement, can any one help me please .

Public Function AddNewProduct(ByVal ProductID As String, ByVal ProductName As String, _

ByVal Color As String, ByVal Size As String, ByVal CountryOfOrigin As String) As OleDbCommand

Dim techConnection As OleDbConnection = New OleDbConnection(strConn)

Dim techCommand As OleDbCommand

techCommand = New OleDbCommand("INSERT INTO Products (ProductID,ProductName,Color,Size,CountryOfOrigin) VALUES (@ProductID,@ProductName,@Color,@Size,@CountryOfOrigin)", techConnection)

techCommand.CommandType = CommandType.Text

techCommand.Parameters.Add("@ProductID", OleDbType.VarChar, 25, "ProductID").Value = ProductID

techCommand.Parameters.Add("@ProductName", OleDbType.VarChar, 25, "ProductName").Value = ProductName

techCommand.Parameters.Add("@Color", OleDbType.VarChar, 25, "Color").Value = Color

techCommand.Parameters.Add("@Size", OleDbType.VarChar, 20, "Size").Value = Size

techCommand.Parameters.Add("@CountryOfOrigin", OleDbType.VarChar, 15, "CountryOfOrigin").Value = CountryOfOrigin

Try

techConnection.Open()

techCommand.ExecuteNonQuery()

MsgBox("Records saved", MsgBoxStyle.Information)

Catch ex As Exception

MsgBox(ex.Message)

End Try

End Function

and calling Sub:-

Private Sub AddNewProduct()

Dim techCommand As OleDbCommand

Try

Dim AddNewProductData As TechManagement.DBComponents.ProductDB = New TechManagement.DBComponents.ProductDB

techCommand = New OleDbCommand

techCommand = AddNewProductData.AddNewProduct(txtProdID.Text, txtProdName.Text, txtColor.Text, txtSize.Text, txtCountryOfOrigin.Text)

techCommand.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub



Re: Visual Basic General Error in Syntax insert into Statement

tkerns

Try getting rid of the 'sourceColumn' arguments like this:

techCommand.Parameters.Add("@ProductID", OleDbType.VarChar, 25).Value = ProductID

techCommand.Parameters.Add("@ProductName", OleDbType.VarChar, 25).Value = ProductName

techCommand.Parameters.Add("@Color", OleDbType.VarChar, 25).Value = Color

techCommand.Parameters.Add("@Size", OleDbType.VarChar, 20).Value = Size

techCommand.Parameters.Add("@CountryOfOrigin", OleDbType.VarChar, 15).Value = CountryOfOrigin

Tony





Re: Visual Basic General Error in Syntax insert into Statement

dotnetsr

So what's the other way to get rid of 'sourceColumn' arguments. can you explain me pls. I would very much appreciate if there is some sample code to demonstrate this.

Thanks





Re: Visual Basic General Error in Syntax insert into Statement

tkerns

The 'sourceColumn' argument is the fourth argument in your original techCommand.Parameters.Add statement. I have already removed them for you in my last post. They are used to automatically retrieve the parameter value from a DataSet. Since you are setting the Value directly, you shouldn't need them. Try that and see if it works.

Tony





Re: Visual Basic General Error in Syntax insert into Statement

dotnetsr

Thanks Tony,

I tried it, But it is not working. Also it is not giving any response. If I put "msgbox(ex.message) " in try catch then it gives Syntax ERROR in INSERT INTO Statement. I don know what is the problem.





Re: Visual Basic General Error in Syntax insert into Statement

tkerns

Sorry, I should have noticed this the first time. I believe that "SIZE" may be a reserved word. Try placing brackets around it like this:

techCommand = New OleDbCommand("INSERT INTO Products (ProductID,ProductName,Color,[Size],CountryOfOrigin) VALUES (@ProductID,@ProductName,@Color,@Size,@CountryOfOrigin)", techConnection)

If that doesn't work, you may need to change the name of the column.

Tony





Re: Visual Basic General Error in Syntax insert into Statement

dotnetsr

Thank you very much for your reply.

Yes I discovered it yesterday. not only "size" even "ProductName" might be reserved word so I removed both column and  and adjusted some code and it is working. Thanks for  giving your valuable time. here I provide the code in order to help others who has similar problem.

Public Function AddNewProduct(ByVal ProductID As String, ByVal ProdName As String, _

ByVal Color As String) As OleDbCommand

 

 

Dim techConnection As OleDbConnection = New OleDbConnection(strConn)

Dim techCommand As OleDbCommand

 

techCommand = New OleDbCommand("INSERT INTO Products (ProductID,ProdName,Color) VALUES (@ProductID,@ProdName,@Color)", techConnection)

techCommand.CommandType = CommandType.Text

techCommand.Parameters.Add("@ProductID", OleDbType.VarChar, 25).Value = ProductID

techCommand.Parameters.Add("@ProdName", OleDbType.VarChar, 25).Value = ProdName

techCommand.Parameters.Add("@Color", OleDbType.VarChar, 25).Value = Color

Try

techConnection.Open()

techCommand.ExecuteNonQuery()

MsgBox("Records saved", MsgBoxStyle.Information)

Catch ex As Exception

End Try

End Function

Private Sub AddNewProduct()

Try

Dim AddNewProductData As TechManagement.DBComponents.ProductDB = New TechManagement.DBComponents.ProductDB

AddNewProductData.AddNewProduct(txtProdID.Text, txtProdName.Text, txtColor.Text)

Catch ex As Exception

MessageBox.Show(ex.Message, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub