K
Kate
I have a Visual Basic Interface that collects data from users and an access
database to store the data in. My problem is getting the data to the db. I'm
new to ADO.NET and have never done it before.
The following code is where I've got to now, I seem to be going round and
round in circles and not getting anywhere fast!! Can anyone spot the error
of my ways, it's probably really obvious!
Public strConn As New
String("Provider=Microsoft.Jet.OLEDB.4.0;Password="";User
ID=Admin;Data Source=C:\Documents and Settings\kate\My
Documents\Databases\JobLogging.mdb;Mode=Share Deny None;Extended
Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry
Path="";Jet OLEDBatabase Password="";Jet OLEDB:Engine Type=5;Jet
OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False")
Public cn As New OleDbConnection(strConn)
Public ds As New DataSet()
Public Function InsertData() As OleDbCommand
Dim strSQL As String
cn.Open()
strSQL = "SELECT RoomNo, StaffName, DateTime, Problem, SerialNo,
Reboot FROM tblLog "
Dim daLog As New OleDbDataAdapter(strSQL, cn)
strSQL = "SELECT Details FROM tblDetails "
Dim daDetails As New OleDbDataAdapter(strSQL, cn)
daLog.Fill(ds, "tblLog")
daDetails.Fill(ds, "tblDetails")
ds.Relations.Add("LogDetails", ds.Tables("Log").Columns("JobNo"), ds
..Tables ("Details").Columns("JobNo"))
strSQL = "INSERT INTO [tblLog] " & _
" (RoomNo, StaffName, DateTime, Problem, SerialNo,
Reboot) " & _
" VALUES (?, ?, ?, ?, ?, ?)"
Dim cmd As New OleDbCommand(strSQL, cn)
Dim pc As OleDbParameterCollection = cmd.Parameters
pc.Add(roomNo, OleDbType.Char, 0, "RoomNo")
pc.Add(staffName, OleDbType.Char, 0, "StaffName")
pc.Add(dateTime, OleDbType.DBTimeStamp, 0, "DateTime")
pc.Add(problem, OleDbType.Char, 0, "Problem")
pc.Add(serialNo, OleDbType.Char, 0, "SerialNo")
pc.Add(reboot, OleDbType.Boolean, 0, "Reboot")
cmd.ExecuteNonQuery()
Return cmd
cn.Close()
End Function
TIA
Kate
database to store the data in. My problem is getting the data to the db. I'm
new to ADO.NET and have never done it before.
The following code is where I've got to now, I seem to be going round and
round in circles and not getting anywhere fast!! Can anyone spot the error
of my ways, it's probably really obvious!
Public strConn As New
String("Provider=Microsoft.Jet.OLEDB.4.0;Password="";User
ID=Admin;Data Source=C:\Documents and Settings\kate\My
Documents\Databases\JobLogging.mdb;Mode=Share Deny None;Extended
Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry
Path="";Jet OLEDBatabase Password="";Jet OLEDB:Engine Type=5;Jet
OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False")
Public cn As New OleDbConnection(strConn)
Public ds As New DataSet()
Public Function InsertData() As OleDbCommand
Dim strSQL As String
cn.Open()
strSQL = "SELECT RoomNo, StaffName, DateTime, Problem, SerialNo,
Reboot FROM tblLog "
Dim daLog As New OleDbDataAdapter(strSQL, cn)
strSQL = "SELECT Details FROM tblDetails "
Dim daDetails As New OleDbDataAdapter(strSQL, cn)
daLog.Fill(ds, "tblLog")
daDetails.Fill(ds, "tblDetails")
ds.Relations.Add("LogDetails", ds.Tables("Log").Columns("JobNo"), ds
..Tables ("Details").Columns("JobNo"))
strSQL = "INSERT INTO [tblLog] " & _
" (RoomNo, StaffName, DateTime, Problem, SerialNo,
Reboot) " & _
" VALUES (?, ?, ?, ?, ?, ?)"
Dim cmd As New OleDbCommand(strSQL, cn)
Dim pc As OleDbParameterCollection = cmd.Parameters
pc.Add(roomNo, OleDbType.Char, 0, "RoomNo")
pc.Add(staffName, OleDbType.Char, 0, "StaffName")
pc.Add(dateTime, OleDbType.DBTimeStamp, 0, "DateTime")
pc.Add(problem, OleDbType.Char, 0, "Problem")
pc.Add(serialNo, OleDbType.Char, 0, "SerialNo")
pc.Add(reboot, OleDbType.Boolean, 0, "Reboot")
cmd.ExecuteNonQuery()
Return cmd
cn.Close()
End Function
TIA
Kate