Results 1 to 15 of 15

Thread: [RESOLVED] Writing to blank Access database

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Location
    indiana
    Posts
    341

    Resolved [RESOLVED] Writing to blank Access database

    I have searched all over the forums and not found an answer to my question. My question is how do i write to an access database at runtime. The database is also created at runtime. I have a dataset loaded with a table that i needs to be written to the database. how would i write each row of my dataset to my database? Sorry if this sounds a little jumbled together it is late and i am tired. If it is unclear you can ask me and i will answer any questions you have about my question.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Writing to blank Access database

    You presumably haven't looked at the Database Development forum because it has an FAQ thread at the top that provides links to threads that explain how to use ADO.NET. If your database contains not table to begin with then you must first build the schema, which you would do by executing the appropriate DDL statements, e.g. CREATE TABLE. The MSDN Library has a full Jet SQL reference and so does Access, so you can get the appropriate syntax from there. The aforementioned ADO.NET threads will show you how to execute a SQL statement.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Location
    indiana
    Posts
    341

    Re: Writing to blank Access database

    Ok. I will look at that Thread. Now i have another question. If i have the XML schema can i use that to create the MS access schema? Because I used readxml to fill my dataset. And i already have access to the xml schema. sorry if this question sounds stupid i am new to database stuff. But to me it seems like it might work.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Writing to blank Access database

    The XML can be used to build the DataSet but, as far as I'm aware, Access (or, rather, Jet) provides no support for automatically creating a schema based on that. The Access documentation would be the place t look for a definitive answer to that. If there is no such support then you will have to use the schema of your DataSet to create the schema of your database. You have to loop through the Tables and then loop through each table's Columns and build your SQL code dynamically.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Location
    indiana
    Posts
    341

    Re: Writing to blank Access database

    Right now that sounds like another language. I will make sure to look at the documentation for access and i will look up how to loop through my dataset to make the access schema tomorrow.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Location
    indiana
    Posts
    341

    Re: Writing to blank Access database

    I figured out how to create the table in a blank database but i dont know how to loop through the rows to fill that table. How would i fill the rows in my database from the dataset?

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Location
    indiana
    Posts
    341

    Re: Writing to blank Access database

    This is the code i tried to use to loop through my datagridview.

    Code:
                Dim AccessConnection As New OleDbConnection()
                AccessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & filename & ";Password=;" 'WHERE Order_Details.OrderID=[@OrderID]AND Order_Details.OrderDetailID=[@OrderDetailID]
                Dim SqlStr As String = "UPDATE " & username & " SET " & username & ".NAME = [@NAME]," & username & ".TCSid = [@TCSid]," & username & ".MFG=[@MFG]," & username & ".QTY=[@QTY]," & username & ".BRANDid=[@BRANDid] "
                AccessConnection.Open()
                Dim cmd As New OleDbCommand(SqlStr, AccessConnection)
                cmd.CommandType = CommandType.Text
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
                    cmd.Parameters.Add("@NAME", Data.OleDb.OleDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells(0).Value
                    cmd.Parameters.Add("@TCSid", Data.OleDb.OleDbType.VarChar, 10).Value = DataGridView1.Rows(i).Cells(1).Value
                    cmd.Parameters.Add("@MFG", Data.OleDb.OleDbType.Integer, 10).Value = DataGridView1.Rows(i).Cells(2).Value
                    cmd.Parameters.Add("@QTY", Data.OleDb.OleDbType.Integer, 10).Value = DataGridView1.Rows(i).Cells(3).Value
                    cmd.Parameters.Add("@BRANDid", Data.OleDb.OleDbType.Integer, 3).Value = DataGridView1.Rows(i).Cells(4).Value
                    cmd.ExecuteNonQuery()
                Next i
                'Debug.Print(intRowsAffected)
                MsgBox("database updated")
                AccessConnection.Close()
    With this code i get an error on the line highlighted in red. the error is:
    Code:
    FormatException was unhandled
    Failed to convert parameter value from a String to a Int32.

  8. #8
    PowerPoster keystone_paul's Avatar
    Join Date
    Nov 2008
    Location
    UK
    Posts
    3,327

    Re: Writing to blank Access database

    It seems fairly clear - the value of either DataGridView1.Rows(i).Cells(2).Value or Cells(3).Value or Cells(4).Value for one of the rows contains something that cannot be converted to an integer.

    When you get the error message try evaluating those three expressions and it should become obvious what the culprit is.

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Location
    indiana
    Posts
    341

    Re: Writing to blank Access database

    Yeah i wish i could have gotten back here sooner. because i went and looked at my code and found the problem. Now it just wont update my table i don't know why either.

    NEW CODE

    Code:
    If table_exists(username, filename) = False Then
                Dim intRowsAffected As Integer = 0
    
                Dim AccessConnection As New OleDbConnection()
                AccessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & filename & ";Password=;" 'WHERE Order_Details.OrderID=[@OrderID]AND Order_Details.OrderDetailID=[@OrderDetailID]
                Dim SqlStr As String = "UPDATE " & username & " SET " & username & ".NAME = [@NAME]," & username & ".TCSid = [@TCSid]," & username & ".MFG=[@MFG]," & username & ".QTY=[@QTY]," & username & ".BRANDid=[@BRANDid] "
                AccessConnection.Open()
                Dim cmd As New OleDbCommand(SqlStr, AccessConnection)
                cmd.CommandType = CommandType.Text
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
                    cmd.Parameters.Add("@NAME", Data.OleDb.OleDbType.VarChar, 50).Value = Convert.ToString(DataGridView1.Rows(i).Cells(0).Value)
                    cmd.Parameters.Add("@TCSid", Data.OleDb.OleDbType.VarChar, 10).Value = Convert.ToString(DataGridView1.Rows(i).Cells(1).Value)
                    cmd.Parameters.Add("@MFG", Data.OleDb.OleDbType.VarChar, 10).Value = DataGridView1.Rows(i).Cells(2).Value
                    cmd.Parameters.Add("@QTY", Data.OleDb.OleDbType.Integer, 10).Value = Convert.ToInt32(DataGridView1.Rows(i).Cells(3).Value)
                    cmd.Parameters.Add("@BRANDid", Data.OleDb.OleDbType.Integer, 3).Value = Convert.ToInt32(DataGridView1.Rows(i).Cells(4).Value)
                    intRowsAffected = cmd.ExecuteNonQuery()
                Next i
                MsgBox(intRowsAffected)
                MsgBox("database updated")
                AccessConnection.Close()
            End If
    Everything executes fine. it works and i don't get any errors. The only problem is that it doesn't update the DB. Anyone know why?

  10. #10
    PowerPoster keystone_paul's Avatar
    Join Date
    Nov 2008
    Location
    UK
    Posts
    3,327

    Re: Writing to blank Access database

    Is IntRowsAffected showing that it is updating rows?

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Location
    indiana
    Posts
    341

    Re: Writing to blank Access database

    No it doesnt show that the rows are updated. IntRowsAffected=0 is what the msgbox says it is after my for loop has ended. Most of that code was taken from http://www.vbforums.com/showthread.p...tabase+dataset and then modified. according to that thread if IntRowsAffected=0 then te rows were not updated. if IntRowsAffected=1 then the rows were updated. But that thread isnt resolved and i don't know that that code works 100% of the time.

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Location
    indiana
    Posts
    341

    Re: Writing to blank Access database

    Ok i have gotten the previous error working. My code now writes to the database but it will only write the first record over and over. i dont see the problem so i will post it here and continue to look it over.

    Code:
     If table_exists(username, filename) = True Then
                Dim AccessConnection As New OleDbConnection()
                AccessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & filename & ";Password=;" 'WHERE Order_Details.OrderID=[@OrderID]AND Order_Details.OrderDetailID=[@OrderDetailID]
                Dim SqlStr As String = "INSERT INTO " & username & "(NAME,TCSid,MFG,QTY,BRANDid) VALUES([@NAME],[@TCSid],[@MFG],[@QTY],[@BRANDid])"
                Dim intRowsAffected As Integer = 0
                Dim cmd As New OleDbCommand(SqlStr, AccessConnection)
                cmd.CommandType = CommandType.Text
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
    
                    cmd.Parameters.Add("@NAME", Data.OleDb.OleDbType.VarChar, 50).Value = Convert.ToString(DataGridView1.Rows(i).Cells(0).Value)
                    cmd.Parameters.Add("@TCSid", Data.OleDb.OleDbType.VarChar, 10).Value = Convert.ToString(DataGridView1.Rows(i).Cells(1).Value)
                    cmd.Parameters.Add("@MFG", Data.OleDb.OleDbType.VarChar, 10).Value = Convert.ToString(DataGridView1.Rows(i).Cells(2).Value)
                    cmd.Parameters.Add("@QTY", Data.OleDb.OleDbType.VarChar, 10).Value = Convert.ToString(DataGridView1.Rows(i).Cells(3).Value)
                    cmd.Parameters.Add("@BRANDid", Data.OleDb.OleDbType.VarChar, 3).Value = Convert.ToString(DataGridView1.Rows(i).Cells(4).Value)
                    AccessConnection.Open()
                    intRowsAffected = cmd.ExecuteNonQuery
                    Debug.Print(intRowsAffected)
                    AccessConnection.Close()
                Next i
                MsgBox(intRowsAffected)
                MsgBox("database updated")
            Else
                Dim AccessConnection As New OleDbConnection()
                AccessConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & filename & ";Password=;" 'WHERE Order_Details.OrderID=[@OrderID]AND Order_Details.OrderDetailID=[@OrderDetailID]
                AccessConnection.Open()
                Dim st1 As String
                Dim cmd As New OleDbCommand
                st1 = "CREATE TABLE " & username & " ("
                st1 &= "NAME NVARCHAR(50),"
                st1 &= "TCSid NVARCHAR(10),"
                st1 &= "MFG NVARCHAR(20),"
                st1 &= "QTY NVARCHAR(10),"
                st1 &= "BRANDid NVARCHAR(10))"
                'st1 &= "Downloaded INTEGER DEFAULT 0)"
                cmd.CommandText = st1
                cmd.Connection = AccessConnection
                cmd.ExecuteNonQuery()
                MsgBox("table created. ready for your data")
                AccessConnection.Close()
                Dim intRowsAffected As Integer = 0
                Dim SqlStr As String = "INSERT INTO " & username & "(NAME,TCSid,MFG,QTY,BRANDid) VALUES([@NAME],[@TCSid],[@MFG],[@QTY],[@BRANDid])"
                Dim cmd2 As New OleDbCommand(SqlStr, AccessConnection)
                cmd2.CommandType = CommandType.Text
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
                    cmd2.Parameters.Add("@NAME", Data.OleDb.OleDbType.VarChar, 50).Value = Convert.ToString(DataGridView1.Rows(i).Cells(0).Value)
                    cmd2.Parameters.Add("@TCSid", Data.OleDb.OleDbType.VarChar, 10).Value = Convert.ToString(DataGridView1.Rows(i).Cells(1).Value)
                    cmd2.Parameters.Add("@MFG", Data.OleDb.OleDbType.VarChar, 10).Value = DataGridView1.Rows(i).Cells(2).Value
                    cmd2.Parameters.Add("@QTY", Data.OleDb.OleDbType.Integer, 10).Value = Convert.ToInt32(DataGridView1.Rows(i).Cells(3).Value)
                    cmd2.Parameters.Add("@BRANDid", Data.OleDb.OleDbType.Integer, 3).Value = Convert.ToInt32(DataGridView1.Rows(i).Cells(4).Value)
                    AccessConnection.Open()
                    intRowsAffected = cmd2.ExecuteNonQuery
                    Debug.Print(intRowsAffected)
                    AccessConnection.Close()
                Next i
                MsgBox(intRowsAffected)
                MsgBox("database updated")
            End If

    Sorry if my code is sloppy. Right now i am just trying to get it to work.

  13. #13
    PowerPoster keystone_paul's Avatar
    Join Date
    Nov 2008
    Location
    UK
    Posts
    3,327

    Re: Writing to blank Access database

    I'm not really an expert on OLEDB, but it looks to me like every time around the loop in the first section you are adding parameters to your command. I'd guess that would work first time round the loop but subsequent iterations those original values will still be there and you're just tagging new ones on to the end. These are probably getting ignored.

    This is the block I'm referring to :
    Code:
                For i As Integer = 0 To DataGridView1.Rows.Count - 1
    
                    cmd.Parameters.Add("@NAME", Data.OleDb.OleDbType.VarChar, 50).Value = Convert.ToString(DataGridView1.Rows(i).Cells(0).Value)
                    cmd.Parameters.Add("@TCSid", Data.OleDb.OleDbType.VarChar, 10).Value = Convert.ToString(DataGridView1.Rows(i).Cells(1).Value)
                    cmd.Parameters.Add("@MFG", Data.OleDb.OleDbType.VarChar, 10).Value = Convert.ToString(DataGridView1.Rows(i).Cells(2).Value)
                    cmd.Parameters.Add("@QTY", Data.OleDb.OleDbType.VarChar, 10).Value = Convert.ToString(DataGridView1.Rows(i).Cells(3).Value)
                    cmd.Parameters.Add("@BRANDid", Data.OleDb.OleDbType.VarChar, 3).Value = Convert.ToString(DataGridView1.Rows(i).Cells(4).Value)
                    AccessConnection.Open()
                    intRowsAffected = cmd.ExecuteNonQuery
                    Debug.Print(intRowsAffected)
                    AccessConnection.Close()
                Next i
    Can you not either clear the parameters collection, or create a new command each time round the loop?

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Location
    indiana
    Posts
    341

    Re: Writing to blank Access database

    I think i can clear the parameter list. I will try it and get back to you. I never would have thought of that thanks

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Feb 2007
    Location
    indiana
    Posts
    341

    Re: Writing to blank Access database

    YAY! this worked. At the end of my for loop i put cmd.clear and everything works GREAT!. thanks so much for you help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width