-
May 16th, 2009, 11:29 PM
#1
Thread Starter
Hyperactive Member
[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.
-
May 16th, 2009, 11:47 PM
#2
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.
-
May 16th, 2009, 11:56 PM
#3
Thread Starter
Hyperactive Member
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.
-
May 17th, 2009, 12:24 AM
#4
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.
Last edited by jmcilhinney; May 17th, 2009 at 12:29 AM.
-
May 17th, 2009, 12:28 AM
#5
Thread Starter
Hyperactive Member
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.
-
May 17th, 2009, 09:53 AM
#6
Thread Starter
Hyperactive Member
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?
-
May 17th, 2009, 11:13 AM
#7
Thread Starter
Hyperactive Member
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.
-
May 17th, 2009, 11:23 AM
#8
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.
-
May 17th, 2009, 11:30 AM
#9
Thread Starter
Hyperactive Member
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?
-
May 17th, 2009, 11:40 AM
#10
Re: Writing to blank Access database
Is IntRowsAffected showing that it is updating rows?
-
May 17th, 2009, 11:50 AM
#11
Thread Starter
Hyperactive Member
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.
-
May 17th, 2009, 02:18 PM
#12
Thread Starter
Hyperactive Member
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.
-
May 17th, 2009, 02:58 PM
#13
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?
-
May 17th, 2009, 03:15 PM
#14
Thread Starter
Hyperactive Member
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
-
May 17th, 2009, 03:27 PM
#15
Thread Starter
Hyperactive Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|