How to access an SQL 2005 DB in VB.Net

[ShawnCoutts]

I am a relatively new user to VB.NET, but have worked extensively in VB 6. In my program, using the server explorer, I have created an sqlconnection object and a data adapter for each table in my database. The problem I am having comes when I try to use an insert command to insert a new record into my database. The syntax of my command is correct, but I continually get the following error:

System.Data.OleDb.OleDbException: Must declare the scalar variable "@MillCode".

Now this variable has been declared as I have made sure to add a parameter for each value being inserted. I have been working on this for the better part of a day, and am getting quite frustrated with the whole thing. Any help would be greatly appreciated.

Shawn

 

[woogoo]

Hi you should check the stored procedure used for your insert statement.

 

[pmegan]

Hi Shawn,

It sounds like you're using the data adapter's insert command. I've always found that to be a big frustrating pain. You can try using a CommandBuilder to make the insert statement for you. I have an unfounded mistrust of them in the final product so don't leave them in, but you can put a breakpoint and watch on it, step through and copy the insert command it generates and assign it to your data adapter's insert command.

Good luck,
Pat

 

[cjelec]

Are you using the drag/drop objects on the form its self to perform database actions, with a DataGrid?

Also can you post the sql command, you can find this in the Properties window: DataAdapter.InsertCommand.CommandText?

Also I noticed, you are using a SqlConnection, but you have used OleDbDataAdapter, I think you do need to use the SqlDataAdapter for it to work properly anyway.

 

[ShawnCoutts]

what I actually was using was the oledbConnection and the oledbDataAdapter and yes i was using the drag and drop components. I did manage to solve my problem. The oledbDataAdapter doesnt really like paramaters in its queries, so once i took out the paramaters and directly accessed the data i was trying to insert, everything worked out fine. Thanks to all for their help.

Shawn