I am trying to use an insert into command, but getting error in insert statement. Can anybody please tell me whats wrong with following code:

Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO [Joint Coordinates](Joint, CoordSys, CoordType, XorR, Y, Z, SpecialJt, GlobalX, GlobalY, GlobalZ, GUID) Values('" & dgr1.Cells(0).Value & "','" & dgr1.Cells(1).Value & "', '" & dgr1.Cells(2).Value & "', '" & dgr1.Cells(3).Value & "', '" & dgr1.Cells(4).Value & "', '" & dgr1.Cells(5).Value & "', '" & dgr1.Cells(6).Value & "', '" & dgr1.Cells(7).Value & "', '" & dgr1.Cells(8).Value & "', '" & dgr1.Cells(9).Value & "', '" & dgr1.Cells(10).Value & "')", con)

what is the easiest way to transfer values from child form to parent form?

modified on Thursday, April 30, 2009 7:10 AM

thanks for your answer. Could you please give me an example?

I just wonder if there is any universal way to make a child form accepting values from parent form and parent form accepting values from child form. I am using visual studio 2008.

:)

Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=" & path1 & "")

above code works. Thank you very very much

thanks i got it. so what should be the data source in:

Dim path1 As String = Path.GetFileName(OpenFileDialog1.FileName)
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=???")

i think i got it. But I am confused about the data source to be use in oledbconnection:

Path.GetFileName(OpenFileDialog1.FileName)
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=???")

that means my no need to establish a connection. Do you mean to do something like: Dim fi As New FileInfo(OpenFileDialog1.FileName) path.GetFileName(&fi.name) Dim objCmdSelect As New OleDbCommand("select * from [Joint Restraint Assignments]" .....) ??? or, path.getfilename(openfiledialog1.filename) Dim objCmdSelect As New OleDbCommand("select * from [Joint Restraint Assignments]" .....)

but my file location is not fixed. I want users to select the access database file and display a table from it into datagridview.

sorry I didn't get it. Could you please give me an example?

thanks. what can be an alternative to fileInfo?

thanks. no i m not. the access database as well as the table is not open.

thanks for your suggestions but I think you misunderstood my problem. Following is complete code and its not working:

If OpenFileDialog1.ShowDialog(Me) = DialogResult.OK Then
Dim fi As New FileInfo(OpenFileDialog1.FileName)
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=;Data Source=" & fi.DirectoryName
Dim objConn As New OleDbConnection(sConnectionString)
Dim objDataSet As DataSet = New DataSet()
objConn.Open()
Dim objCmdSelect As New OleDbCommand("select * from [Joint Restraint Assignments]" & fi.Name, objConn)
Dim objAdapter1 As New OleDbDataAdapter
objAdapter1.SelectCommand = objCmdSelect
objAdapter1.Fill(objDataSet, "test")
DataGridView1.DataSource = objDataSet.Tables(0).DefaultView
objConn.Close()
End If

I just wonder what's wrong with this code, in particular when i want to read access database file.

Hello everyone, I just wonder what's wrong with my code. Actually I want to use openfiledialog to locate an access database file and then display it in datagridview. I used following connection string:

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=;Data Source=" & fi.DirectoryName

and following to call the table

Dim objCmdSelect As New OleDbCommand("select * from table1" & fi.Name, objConn)

But I get the famous error: "the microsoft jet database engine cannot open the file. it is already opened exclusively by another user or you need permission to view its data." But neither by database is open nor its password protected. Please help me resolving this problem

got it got it! Thank you so much. I am all done! :laugh:

But I am getting two blank rows. One is the obvious one but I am getting one another row on the top of the default blank row.

Thank you very very much. Following code works:

Try
Dim dgr1 As DataGridViewRow

        For Each dgr1 In Me.DataGridView1.Rows

            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\\db1.mdb")

            Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values('" & dgr1.Cells(0).Value & "', '" & dgr1.Cells(1).Value & "', '" & dgr1.Cells(2).Value & "', '" & dgr1.Cells(3).Value & "', '" & dgr1.Cells(4).Value & "', '" & dgr1.Cells(5).Value & "', '" & dgr1.Cells(6).Value & "')", con)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        Next
    Catch ex As Exception
        MsgBox(ex.ToString)  

End Try

But I just wonder why I am getting one extra blank row in my database table.

When I did like this:

Try
Dim dgr1 As DataGridViewRow

        For Each dgr1 In Me.DataGridView1.Rows

            Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;data source=|DataDirectory|\\db1.mdb")

            Dim cmd As OleDbCommand = New OleDbCommand("INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values(" & dgr1.Cells(0) & ", " & dgr1.Cells(1) & ", " & dgr1.Cells(2) & ", " & dgr1.Cells(3) & ", " & dgr1.Cells(4) & ", " & dgr1.Cells(5) & ", " & dgr1.Cells(6) & ")", con)
       
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        Next
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

It said: Error 1 Operator '&' is not defined for types 'String' and 'System.Windows.Forms.DataGridViewCell'. I don't know what values I should enter. This must be a simple stuff but I am just struck.

I tried:

"INSERT INTO Sheet1(Field1, Field2, Field3, Field4, Field5, Field6, Field7) Values(" & dgr1.Cells(0) & ", " & dgr1.Cells(1) & ", " & dgr1.Cells(2) & ", " & dgr1.Cells(3) & ", " & dgr1.Cells(4) & ", " & dgr1.Cells(5) & ", " & dgr1.Cells(6) & ", " & dgr1.Cells(7) & " )"

but it says syntax error