Insert data in VB.Net

[Pankaj]

Hi
i am inserting the data into database throught variables. On click
event of button m using this code:

Dim objCon As New OleDbConnection(CONNECTION)
Dim objCmd As New OleDbCommand(SQL, objCon)

SQL = "INSERT INTO linkinfo VALUES('" & Sno & "','" & Website & "','" &
Path & "')" & _
"('" & Keywords & "','" & MeWebsite & "','" & MePath & "','" &
MeKeywords & "')" & _
"('" & ContactName & "','" & ContactEMail & "','" & Createdate & "','"
& ModifiedDate & "')"

objCmd.ExecuteNonQuery()


when i click on command button it gives error in sql statement. so
please help me with solution code

thanks in advance

 

[latin & geek via DotNetMonster.com]

hi pankaj. i found this easier...

Dim mylink As New OleDb.OleDbConnection()
'the data connection link

Dim ds As New DataSet()
'data set for temp storage so manipulating data is possible

Dim da As OleDb.OleDbDataAdapter
'data adapter speaks to the DB for the DS.

Dim sql As String
'sql string needed for setting up DA

'setting up connection link to database. service provider: JET. path to
file specified.
mylink.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data
Source = C:\GEorders.mdb"
mylink.Open() 'opening connection

sql = "SELECT * FROM GEorders" 'selecting all columns in the
table "GEorders"
da = New OleDb.OleDbDataAdapter(sql, mylink) 'building new data
adaptor to link database to '_connection
object

da.Fill(ds, "GEorders") 'filling the dataset with
details from the table

'adding a new row of data

'need command builder to build sql string.
Dim cb As New OleDb.OleDbCommandBuilder(da)

'creating a variable to open new blank row in the dataset
Dim dsNewRow As DataRow
'opening new blank row
dsNewRow = ds.Tables("GEorders").NewRow()

'assigning the individual cell values to the blank cells in the row

dsNewRow.Item("Cust") = txtCust.Text
dsNewRow.Item("Dat") = txtDat.Text
ds.Tables("GEorders").Rows.Add(dsNewRow)

'the data adaptor is asked to contact data base table to update. it
is able to execute the order
' because the command builder has built the SQL string.
Try
da.Update(ds, "GEorders")
Catch ex As Exception
MsgBox("Error: " & ex.Source & ":" & ex.Message, MsgBoxStyle.
OKOnly)
End Try
MsgBox("New Record added to the Database")

and that should do it.

 

[Cyril Gupta]

Hmm... You have a peculiar SQL statement written right there.

I don't understand why you have so many brackets opening and closing.
Brackets should open and close only in the beginning and end of your
values...

Regards
Cyril Gupta

 

[Armin Zingler]

Hi
i am inserting the data into database throught variables. On click
event of button m using this code:

Dim objCon As New OleDbConnection(CONNECTION)
Dim objCmd As New OleDbCommand(SQL, objCon)

SQL = "INSERT INTO linkinfo VALUES('" & Sno & "','" & Website &
"','" & Path & "')" & _
"('" & Keywords & "','" & MeWebsite & "','" & MePath & "','" &
MeKeywords & "')" & _
"('" & ContactName & "','" & ContactEMail & "','" & Createdate &
"','" & ModifiedDate & "')"

objCmd.ExecuteNonQuery()


when i click on command button it gives error in sql statement. so
please help me with solution code

If you create a new SQL statement after you assigned the old one, which is
still empty, the OleDbCommand still uses the old one. That's one problem
with initialization in the line of declaration. I didn't examine the SQL
statement itself, but the order should be:

Dim objCmd As OleDbCommand

SQL = "INSERT INTO linkinfo VALUES('" & Sno & "','" & Website & "','" &
Path & "')" & _
"('" & Keywords & "','" & MeWebsite & "','" & MePath & "','" &
MeKeywords & "')" & _
"('" & ContactName & "','" & ContactEMail & "','" & Createdate & "','"
& ModifiedDate & "')"

objCmd = New OleDbCommand(SQL, objCon)

objCmd.ExecuteNonQuery()


You should also consider using the OleDBCommand's Parameters property.
Prevents you from building the string on your own. Be aware that then you
don't have a chance to see the final SQL that is sent to the database from
within the IDE and while debugging.


Armin

 

[Herfried K. Wagner [MVP]]

i am inserting the data into database throught variables. On click
event of button m using this code:

Dim objCon As New OleDbConnection(CONNECTION)
Dim objCmd As New OleDbCommand(SQL, objCon)

SQL = "INSERT INTO linkinfo VALUES('" & Sno & "','" & Website & "','" &
Path & "')" & _
"('" & Keywords & "','" & MeWebsite & "','" & MePath & "','" &
MeKeywords & "')" & _
"('" & ContactName & "','" & ContactEMail & "','" & Createdate & "','"
& ModifiedDate & "')"

objCmd.ExecuteNonQuery()

Use a parameterized command object instead, which will perform escaping
automatically.

Sample:

<URL:http://www.vb-tips.com/?ID=550279ec-6767-44ff-aaa3-eb8b44af0137>

 

[Jim Wooley]

I can see 3 problems with the code:
1) All of the values you are inserting should be included within a
single (...) block. You have three in this example.
2) by dynamically creating your SQL string, you are opening yourself up
to SQL Injection which may or may not apply in your specific Database (I
assume you are not using SQL Server since you are using the OleDb objects.)
You should still use parameters as in:
SQL = "INSERT INTO linkinfo VALUES @Sno, @Website, @Path ..."
objCmd.Parameters.Add("@Sno", Sno)
objCmd.Parameters.Add("@Website", Website)
etc.... (btw if in 2005, use objCmd.Parameters.AddWithValue instead)
3) This is the main reason your code errors. Since you are setting the
sql string in the objCmd constructor, you are setting the SQL to "". You
never reset it after actually building the SQL.

Jim Wooley