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

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.

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

Show quote Hide quote 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

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

Sample:

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

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

Show quoteHide quote

Copying one project to another
create user and add to admin group under local system
Run code under diffrent user
Calling Java class from VB.net
Cpmute values in Dataset Table??
How to know if Windows Update is ON or OFF?
Can I do this on One line of code?
Collections Woes - Help!
DataGridBoolColumn - Greyed Out
How to insert a page break in multi-page document?