insert record into SQLServer

This is probably very trivial...

my problem is that I want to find out the best way to take the values from two textboxes and insert them into an existing table in a SQL server 2005 database. My initial plan was to do this using an SQL insert query triggered by a click on a button but can not find out how to bind the VB textbox variables to the appropriate value expression in the insert query.

The on-line help provided with visual studio proposes multiple quite extensive approaches, but they seem to be over-elaborate considering the elegance otherwise provided by the environment.

Looking forward to your advice on this!

Erik

.NET Development22

Presuming you have an SQL Connection set up.

1. Create an SQL Command connected to the SQL Connection.
2. Set the Commands .CommandText equal to your insert string.
3. Open the connection
4. Use the Command .ExecuteNonQuery method.

I do not have your code sample so do not know your variable or control names...

Dim myCommand As SqlCommand = New SqlCommand("",<connection variable>)

myComand.CommandText = "INSERT INTO <tablename> VALUES ('"+<textbox1name>.Text _
& "','"+<textbox2name>.Text+"')"

<connection variable>.Open

myCommand.ExecuteNonQuery

<connection variable>.Close

This assumes that there are only two fields in the table. If not you will have to list the field names and have default values for the remaining fields for it to work correctly:

INSERT INTO <table> (<fieldname1>,<fieldname2>) VALUES (.........etc.

Hope this helps.

it is recommended, highly, to use parameterized queries as they are securer and can resolve a few issues, but also using stored procedures is even more recommended. To keep it simple lets modify Rabtok's response :-)

Dim myCommand As SqlCommand = New SqlCommand )",<connection variable>)

myCommand.Parameter.Add(parameter1)

myCommand.Parameter.Add(parameter2)

myCommand.Connection.Open()

myCommand.ExecuteNonQuery()

myCommand.Connection.Close()

Lets us go further and tidy up properly...Once you have finished with it it is nice to Dispose of the command.

myCommand.Dispose

I always forget something.

Ive been trying to do the same.

I have 2 text boxes that i want to use to insert data to a database

DateID which i calculate with a datagrid total rows + 1

Date which will be the value i enter

When i press Submit, i wanna insert those values into the database, here is sample of my code

I get error on the SQLCONNECTION STRING. Keyword not supported: 'provider'. Im a rookie, please help.

Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\data.mdb ")

Dim cmd As New System.Data.SqlClient.SqlCommand

cmd.CommandType = System.Data.CommandType.Text

cmd.CommandText = "INSERT INTO Dates (DateID, Date) VALUES ('" + dateid.Text _

& "','" + actualdate.Text + "')"

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

You cannot use SqlConnection class to connect to Access database. SqlConnection works ONLY with SQL Server. You need to use OledbConnection and other Oledb-related classes in this case. You also need to replace concatenated INSERT statement with parameterized statement to avoid SQL injection vulnerability in a code

Can you fix that code for me, im really new to this and i am trying to understand. Make sense about SQL and access. Now i dont know how to work with ole. Can you do my code into ole> THanks

Ok, i changed it to OLE,here is the error i get

ExecuteNonQuery: Connection property has not been initialized.

here is my code:

Dim oleConnection1 As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\data.mdb ")

Dim cmd As New System.Data.OleDb.OleDbCommand

cmd.CommandType = System.Data.CommandType.Text

cmd.CommandText = "INSERT INTO Dates (DateID, Date) VALUES ('" + dateid.Text _

& "','" + actualdate.Text + "')"

oleConnection1.Open()

cmd.ExecuteNonQuery() <-------- ERROR HERE

oleConnection1.Close()

You have opened connection and created command, but you did not specify for you command which connection to use. You need to use next kind of code

.......

oleConnection1.Open()

cmd.Connection = oleConnection1

cmd.ExecuteNonQuery()

oleConnection1.Close()