Q. Access and Adapter.InsertCommand

[joe_pool_is]

I am trying to use a DataTable as my method of writing to my Access database.

 

I found an example on MSDN (#815629) with the following:

cmdInsert.CommandText = "INSERT INTO AutoIncrementTest (Description) VALUES (?)"
cmdInsert.Connection = cnJetDB
cmdInsert.Parameters.Add(New OleDbParameter("Description", OleDbType.VarChar, 40, "Description"))
oleDa.InsertCommand = cmdInsert ' were oleDa = OleDbDataAdapter

 

Their example is *much* simpler than what I need. Further, their example confuses me on a couple of points:

1) In "cmdInsert.Parameters.Add", there are two "Description" values. Which is the one being read into the "cmdInsert.CommandText" value "(Description)"?

2) Must I add one of these new OleDbParameters for each of the 38 different Parameters that I have in my Table? (Lord, I hope not!)

 

Thanks for helping.

[ilya2]

1) The first one is the parameter name, which is important in this case, the second one is SourceColumn which is used to fill the in-memory datatable back from datasource.

2) Yes, you must supply all parameters you want to be inserted this way. However, you may construct whole sql query in CommandText, such as

cmdInsert.CommandText = "INSERT INTO Table VALUES(" + id.ToString() +",'" + desc +"')"

But I still think using parameters is simpler because you don't need to care about data formatting.

[joe_pool_is]

Ilya,

Thanks for the reply; I was beginning to believe that no one knew the answer on here.

 

From what I understand from you, I only need one version or the other. Is this correct?

 

While waiting for a response, I went through my code trying my best to modify it according to the Microsoft example (even though my version required *much* more complexity than their example gave). What I came up with is listed below, which I envite you (and anyone else) to comment on.

 

A few notes:

1) The function objDLD.IFF(StatementToCompare, ReturnTrue, ReturnFalse) is something short I wrote that takes in a StatementToCompare and returns ReturnTrue if the StatementToCompare is True, or returns ReturnFalse if StatementToCompare is False. (I think this is built into VB somewhere, but I can't ever get the built in version to work for me)

2) It looks as if I am being redundant in my SQL setup, but this was the way the example showed me. I didn't understand, but it seems to work. I just worked with it.

Private Function WriteToDB(ByVal FirstName As String, ByVal LastName As String, ByVal Phone As String, ByVal ZIP As String, Optional ByVal Email As String = "") As Boolean
Dim i As Integer
Dim Item As ListItem
Dim objDLD As New DropListData
Dim SQLsnub As String = ""
Dim SQL As String = ""
Dim strDB As String = Server.MapPath("database.mdb")
Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDB & ";Persist Security Info=False"
Dim Connection1 As OleDbConnection = New OleDbConnection(strConnection)
Dim Adapter1 As New OleDbDataAdapter("SELECT * FROM Customer1", Connection1)
Dim Insert1 As New OleDbCommand
' Create a DataTable and DataRow
Dim DataTable1 As New DataTable
Adapter1.Fill(DataTable1)
Dim DataRow1 As DataRow
' Command to Insert Records
Insert1.CommandText = "INSERT INTO Customer1 (FirstName, LastName, Address, City, State, Zip, Phone, " & _
					"IDType, IDNum, DOB, ServAddr, ServCity, ServState, ServZip, " & _
					"SSN, Email, CableTV, SatTV, Electric, IAuto, IHome, ILife, " & _
					"IMed, IRent, CallID, CallWait, CallFWD, WaitID, 3Way, " & _
					"VM, LongDist, DialUp, DSL) " & _
					"VALUES " & _
					"(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Insert1.Connection = Connection1
Insert1.Parameters.Add(New OleDbParameter("FirstName", OleDbType.VarChar, 50, "FirstName"))
Insert1.Parameters.Add(New OleDbParameter("LastName", OleDbType.VarChar, 50, "LastName"))
Insert1.Parameters.Add(New OleDbParameter("Address", objDLD.IFF(chkUseSame.Checked, txtAddress2.Text, txtAddress1.Text)))
Insert1.Parameters.Add(New OleDbParameter("City", objDLD.IFF(chkUseSame.Checked, txtCity2.Text, txtCity1.Text)))
Insert1.Parameters.Add(New OleDbParameter("State", objDLD.IFF(chkUseSame.Checked, ddlState2.SelectedItem.Text, ddlState1.SelectedItem.Text)))
Insert1.Parameters.Add(New OleDbParameter("Zip", objDLD.IFF(chkUseSame.Checked, txtZip2.Text, txtZip1.Text)))
Insert1.Parameters.Add(New OleDbParameter("Phone", OleDbType.VarChar, 50, "Phone"))
Insert1.Parameters.Add(New OleDbParameter("IDType", OleDbType.VarChar, 50, "IDType"))
Insert1.Parameters.Add(New OleDbParameter("IDNum", OleDbType.VarChar, 50, "IDNum"))
Insert1.Parameters.Add(New OleDbParameter("DOB", OleDbType.VarChar, 50, "DOB"))
Insert1.Parameters.Add(New OleDbParameter("ServAddr", OleDbType.VarChar, 50, "ServAddr"))
Insert1.Parameters.Add(New OleDbParameter("ServCity", OleDbType.VarChar, 50, "ServCity"))
Insert1.Parameters.Add(New OleDbParameter("ServState", OleDbType.VarChar, 50, "ServState"))
Insert1.Parameters.Add(New OleDbParameter("ServZip", OleDbType.VarChar, 50, "ServZip"))
Insert1.Parameters.Add(New OleDbParameter("SSN", OleDbType.VarChar, 50, "SSN"))
Insert1.Parameters.Add(New OleDbParameter("Email", OleDbType.VarChar, 50, "Email"))
Insert1.Parameters.Add(New OleDbParameter("CableTV", objDLD.IFF(Me.chkCableTV.Checked, True, False)))
Insert1.Parameters.Add(New OleDbParameter("SatTV", objDLD.IFF(Me.chkSatelliteTV.Checked, True, False)))
Insert1.Parameters.Add(New OleDbParameter("Electric", objDLD.IFF(Me.chkElectric.Checked, True, False)))
Insert1.Parameters.Add(New OleDbParameter("IAuto", objDLD.IFF(Me.chkAuto.Checked, True, False)))
Insert1.Parameters.Add(New OleDbParameter("IHome", objDLD.IFF(Me.chkHomeOwners.Checked, True, False)))
Insert1.Parameters.Add(New OleDbParameter("ILife", objDLD.IFF(Me.chkLife.Checked, True, False)))
Insert1.Parameters.Add(New OleDbParameter("IMed", objDLD.IFF(Me.chkMedical.Checked, True, False)))
Insert1.Parameters.Add(New OleDbParameter("IRent", objDLD.IFF(Me.chkRenters.Checked, True, False)))
For Each Item In lstLocalPhone.Items
 Select Case Item.Text
	 Case "Caller ID"
		 Insert1.Parameters.Add(New OleDbParameter("CallID", objDLD.IFF(Item.Selected, True, False)))
	 Case "Call Waiting"
		 Insert1.Parameters.Add(New OleDbParameter("CallWait", objDLD.IFF(Item.Selected, True, False)))
	 Case "Call Forwarding"
		 Insert1.Parameters.Add(New OleDbParameter("CallFWD", objDLD.IFF(Item.Selected, True, False)))
	 Case "Call Waiting ID"
		 Insert1.Parameters.Add(New OleDbParameter("WaitID", objDLD.IFF(Item.Selected, True, False)))
	 Case "3-Way Calling"
		 Insert1.Parameters.Add(New OleDbParameter("3Way", objDLD.IFF(Item.Selected, True, False)))
	 Case "Voicemail"
		 Insert1.Parameters.Add(New OleDbParameter("VM", objDLD.IFF(Item.Selected, True, False)))
 End Select
Next
Insert1.Parameters.Add(New OleDbParameter("LongDist", OleDbType.Boolean, 1, "LongDist"))
Insert1.Parameters.Add(New OleDbParameter("DialUp", OleDbType.Boolean, 1, "DialUp"))
Insert1.Parameters.Add(New OleDbParameter("DSL", OleDbType.Boolean, 1, "DSL"))
Adapter1.InsertCommand = Insert1
' Add rows to DataTable1
DataRow1 = DataTable1.NewRow
DataRow1("FirstName") = FirstName
DataRow1("LastName") = LastName
DataRow1("Address") = objDLD.IFF(chkUseSame.Checked, txtAddress2.Text, txtAddress1.Text)
DataRow1("City") = objDLD.IFF(chkUseSame.Checked, txtCity2.Text, txtCity1.Text)
DataRow1("State") = objDLD.IFF(chkUseSame.Checked, ddlState2.SelectedItem.Text, ddlState1.SelectedItem.Text)
DataRow1("Zip") = objDLD.IFF(chkUseSame.Checked, txtZip2.Text, txtZip1.Text)
DataRow1("Phone") = Phone
DataRow1("IDType") = ddlIDType.SelectedItem.Text
DataRow1("IDNum") = txtIDNumber.Text
DataRow1("DOB") = DOB
DataRow1("ServAddr") = txtAddress1.Text
DataRow1("ServCity") = txtCity1.Text
DataRow1("ServState") = ddlState1.SelectedItem.Text
DataRow1("ServZip") = txtZip1.Text
DataRow1("SSN") = txtSSN.Text
DataRow1("Email") = Email
DataRow1("CableTV") = objDLD.IFF(Me.chkCableTV.Checked, True, False)
DataRow1("SatTV") = objDLD.IFF(Me.chkSatelliteTV.Checked, True, False)
DataRow1("Electric") = objDLD.IFF(Me.chkElectric.Checked, True, False)
DataRow1("IAuto") = objDLD.IFF(Me.chkAuto.Checked, True, False)
DataRow1("IHome") = objDLD.IFF(Me.chkHomeOwners.Checked, True, False)
DataRow1("ILife") = objDLD.IFF(Me.chkLife.Checked, True, False)
DataRow1("IMed") = objDLD.IFF(Me.chkMedical.Checked, True, False)
DataRow1("IRent") = objDLD.IFF(Me.chkRenters.Checked, True, False)
For Each Item In lstLocalPhone.Items
 Select Case Item.Text
	 Case "Caller ID"
		 DataRow1("CallID") = objDLD.IFF(Item.Selected, True, False)
	 Case "Call Waiting"
		 DataRow1("CallWait") = objDLD.IFF(Item.Selected, True, False)
	 Case "Call Forwarding"
		 DataRow1("CallFWD") = objDLD.IFF(Item.Selected, True, False)
	 Case "Call Waiting ID"
		 DataRow1("WaitID") = objDLD.IFF(Item.Selected, True, False)
	 Case "3-Way Calling"
		 DataRow1("3Way") = objDLD.IFF(Item.Selected, True, False)
	 Case "Voicemail"
		 DataRow1("VM") = objDLD.IFF(Item.Selected, True, False)
 End Select
Next
DataRow1("LongDist") = objDLD.IFF(Me.chkLongDistance.Checked, True, False)
DataRow1("DialUp") = objDLD.IFF(Me.chkDialUp.Checked, True, False)
DataRow1("DSL") = objDLD.IFF(Me.chkDSL.Checked, True, False)
DataTable1.Rows.Add(DataRow1)
' Delegate for Handling RowUpdated event
AddHandler Adapter1.RowUpdated, AddressOf HandleRowUpdated
Try
 Command1 = New OleDbCommand
 Command1.CommandText = "SELECT @@IDENTITY" ' // gets the ID number of record added
 Command1.Connection = Connection1
 Command1.Connection.Open()
 Adapter1.Update(DataTable1)				 ' // gnabs our ID while writing the data to DB
 Command1.CommandText = "DROP TABLE Customer1"
 Command1.ExecuteNonQuery()
 Command1.Connection.Close()
 Return True
Catch ex As Exception
 ' // there were errors
 lblError.Visible = True
 lblError.Text &= "Database Save Error."
 exMsg = ex.Message
 Command1.Connection.Close()
 txtClientID.Text = "Error"
 Return False
End Try
'txtClientID.Text = Reader1.Item("ID").ToString
End Function

[cpopham]

When setting up parameters inthis fashion, the code can get quite drawn out. However, in the longtun, this can be more effiecient because all of the parameters can be inserted in the database at the same time. Also if you use a variable and just loop through using a variable in your SQL statement the paramters get inserted one at a time and also this method is less efficient. I have read on this in one or two books and can not remember why using a variable instead of setting up parameters making the program execute less efficiently working with the database.

 

Chester

[joe_pool_is]

This version of inserting data into my database worked great, and it appears very efficient.

 

Now, I would like to learn how to update and read data from my database like this as well.

 

Does anyone know where I can find information on this? What is this type of reading, writing, and inserting technique called? (that will give me something to search under)

 

Best Regards,

Joe

[cpopham]

This is using a parameterized query to update. This is how the Command Builder does it, if you use the command builder. Here is a sample using OLEDB for Access:

 

Dim myCommand2 As New OledbCommand()

 

myCommand2.CommandText = "SELECT * " & _

"FROM Titles " & _

"WHERE Title = ? Or YearPub = ?"

 

' Declare and Instantiate the firat Parameter

Dim prmName as new Oledb.OledbParameter()

' Assign parameter name

prmName.ParameterName = "@Name"

' Assign value to parameter

prmName.Value = txtName.Text

' Specify type of parameter

prmName.Direction = ParameterDirection.Input

' Add the parameter to the parameters collection

myCommand2.Parameters.Add(prmName)

 

' Repeat for next parameter

Dim prmTele As New Oledb.OledbParameter()

prmTele.ParameterName = "@Tele"

prmTele.Vale = txtTelephone.Text

prmDirection = ParameterDirection.Input

myCommand2.Parameters.Add(prmTele)

 

This is using parameters for a SELECT statement, but it is still the same concept for UPDATE and APPEND statements.

 

Hope this helps!

Chester