sending data to 2 related tables

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 OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet
OLEDB:Database 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 OLEDB:Don'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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,731
Messages
2,569,432
Members
44,832
Latest member
GlennSmall

Latest Threads

Top