SQL/VB headache

[Magic Hat]

I cannot for the life of me figure out how to add data to an SQL database using VB (Visual Studio Express) I already have the Dataset but it wont work

ScheduleTableAdapter.insert("test","test","test")

It just wont work, any help would be appreciated.

 

[NinjiaPirate]

Are you calling a stored procedure? A Function? Or it's just streamlined in your code.

You'll need to
1) Create a connection string
2) Create your sql statement "INSERT INTO tablename (columnname,columnname) VALUES (' + variable + ',' + variable ')
2) Create a connection object
3) Open connection object
4) Create the command
5) execute the command
6) close the connection

This is the basics, not the most secure way as you can do sql injections this way.

Also I'm you to Visual Studio 2003 and have not used Visual Studio Express before so I'm not sure if it is different.

This below is for MySQL Server, so you may need to change some of the sql to work with MSSQL

Dim strCONN As String = "[i]your connection string[/i]"
Dim sql As String = "INSERT INTO " & tbl & "Main " & _
"(keyfield, lang) " & _
 "VALUES " & _
"(?keyfield, ?lang)"
' <SET connection object>
Dim objConn As New MySqlConnection(strCONN)
objConn.Open()
' <SET command object>
Dim objCmd As New MySqlCommand
objCmd.Connection = objConn
objCmd.CommandText = sql
objCmd.Prepare()
objCmd.Parameters.Add("?keyfield", keyfield)
objCmd.Parameters.Add("?lang", lang)

objCmd.ExecuteNonQuery()
objConn.Close()

Hopefully this gets you started.

 

[mikeblas]

It's very much different. VB.Net is a different language than VB.

 

[th3rmite]

If you already have the dataset there are several ways you can do it. I'm not even sure if I know all of them because like you everyone always quotes vb2003 when I need help

I am going to assume you want to programatically add a new row to your table and supply the fields from variables.

For this example I'm going to use some names:
"myDataSet" is the dataset
"myTable" is the table

Double click the myDataSet.xsd in the solution explorer. This will open up the view where it shows you the table Adapters in the dataset. btw this is where you can edt the relationships of the tables. Now you will see myTable with a section labeled myTableAdapter. Under this section by default you should see fill,GetData() You need to right click that and the floating menu click on add query. From here you will follow step by step on how to make an insert query with parameters. By defauIt it will have you save it as FillBy,GetDataBy() by default.

Now once you have this setup you insert the data into your table with this line of code:

Me.MyTableAdapter.FillBy(Me.myDataSet.myTable, parameter1, parameter2, ...)

I hope this helps.