import data from vb.net to miscrosoft access

[qwer]

Hi,
im desperately trying to import and store data from visual basic.net to microsoft access. Does anyone has any idea what codes i have to add in apart from adding the .Jet method as shown below?

Private Const cnstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = carpark1.mdb"

If possible can u pls provide some relevant programming or access samples or examples?

U can contact me thru my email: [e-mail]
Thx

[dip_developer]

Hi,
im desperately trying to import and store data from visual basic.net to microsoft access. Does anyone has any idea what codes i have to add in apart from adding the .Jet method as shown below?

Private Const cnstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = carpark1.mdb"

If possible can u pls provide some relevant programming or access samples or examples?

U can contact me thru my email: [e-mail]
Thx

http://visualbasic.ittoolbox.com/gro...tabase-1595578

http://support.microsoft.com/kb/308278

[qwer]

im actually doing on a SMS application using Visual Basic.Net whereby the objective is to allow the user to send a SMS to the visual basic programme and i should decode out the hp no, car plate, time in,time out and date.And finally import the data(hp number, car plate number....as stated earlier) into Miscrosoft Access.I have already successfully decoded the data and May I know how i can import the decoded information/data into Microsoft Acess so that it can be stored as database in order to be retrived in the later part.Below is my programme.
Thanks

Imports System.Data.OleDb

Public Class Form1
Dim WithEvents serialPort As New IO.Ports.SerialPort
Dim Data As String
Dim buffer As String
Dim Handphone_number As String
Dim datetime As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim msg$
Dim Content As String

Const HEADER = """REC UNREAD"""
Const EOM = "EOM"
Private Const cnstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = carpark.mdb"
-------------------------------------------------------------------------------------------------------------------

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


Timer1.Enabled = True

Try
With serialPort
.PortName = "COM12"
.BaudRate = 115200
.Parity = IO.Ports.Parity.None
.DataBits = 8
.StopBits = IO.Ports.StopBits.One
End With
serialPort.Open()

MsgBox(" connected.")
Try
serialPort.Write("ATE" + vbCr)


Catch ex As Exception
MsgBox(ex.ToString)
End Try


Catch ex As Exception
MsgBox(ex.ToString)
End Try


End Sub
-------------------------------------------------------------------------------------------------------------------


Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick

Try
serialPort.Write("AT+CMGL=""REC UNREAD""" & vbCr)

Catch ex As Exception
MsgBox(ex.ToString)
End Try


'MSComm1.Output = "AT+CMGL=""REC UNREAD""" & vbCr
End Sub
--------------------------------------------------------------------------------------------------------------------------------------------

Private Sub Decode(ByVal m As String)
Dim timestr As String
Dim datestr As String
Dim car_plate As String


i = InStr(1, m, """")
If (i > 0) Then
j = InStr(i + 1, m, """")
If (j > 0) Then
Handphone_number = Mid(m, i + 1, j - i - 1)
MsgBox("The Handphone number is:" + Handphone_number)


i = InStr(j + 1, m, """")
If (i > 0) Then
j = InStr(i + 1, m, """")
If (j > 0) Then
datetime = Mid(m, i + 1, j - i - 1)
datestr = Mid(datetime, 1, 8)
timestr = Mid(datetime, 10, 8)
MsgBox("The date is: " + datestr)
MsgBox("The time is: " + timestr)


car_plate = Mid(m, j + 1)
MsgBox("The car plate number is: " + car_plate)

End If
End If

End If
End If
End Sub
-------------------------------------------------------------------------------------------------------------------
Private Sub DataReceived( _
ByVal sender As Object, _
ByVal e As System.IO.Ports.SerialDataReceivedEventArgs) _
Handles serialPort.DataReceived

Dim somemore As Boolean

buffer = buffer + serialPort.ReadExisting
somemore = True
Do While somemore = True

i = InStr(buffer, HEADER)
If (i > 0) Then 'beginning of message found

j = InStr(i, buffer, EOM)
If (j > 0) Then 'end of message found

msg$ = Mid(buffer, i + Len(HEADER), j - i - Len(HEADER))
MsgBox(msg, vbInformation, "TEST")

Call Decode(msg$)
buffer = Mid(buffer, j + Len(EOM))
If Len(buffer) = 0 Then
somemore = False
End If

Else
somemore = False
End If
Else
somemore = False
End If
Loop

End Sub
-------------------------------------------------------------------------------------------------------------------
Private Sub executeDataCommand(ByVal s As String)
Dim cmd As OleDbCommand
Dim cn As New OleDbConnection(cnstr)

cn.Open()
cmd = New OleDbCommand(s, cn)
cmd.ExecuteNonQuery()
End Sub
-------------------------------------------------------------------------------------------------------------------

Private Function executeDataRead(ByVal s As String) As OleDbDataReader
Dim rd As OleDbDataReader
Dim cmd As OleDbCommand
Dim cn As New OleDbConnection(cnstr)

cn.Open()
cmd = New OleDbCommand(s, cn)

rd = cmd.ExecuteReader()
executeDataRead = rd
End Function

End Class
-------------------------------------------------------------------------------------------------------------------

[dip_developer]

im actually doing on a SMS application using Visual Basic.Net whereby the objective is to allow the user to send a SMS to the visual basic programme and i should decode out the hp no, car plate, time in,time out and date.And finally import the data(hp number, car plate number....as stated earlier) into Miscrosoft Access.I have already successfully decoded the data and May I know how i can import the decoded information/data into Microsoft Acess so that it can be stored as database in order to be retrived in the later part.Below is my programme.
Thanks

Imports System.Data.OleDb

Public Class Form1
Dim WithEvents serialPort As New IO.Ports.SerialPort
Dim Data As String
Dim buffer As String
Dim Handphone_number As String
Dim datetime As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim msg$
Dim Content As String

Const HEADER = """REC UNREAD"""
Const EOM = "EOM"
Private Const cnstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = carpark.mdb"
-------------------------------------------------------------------------------------------------------------------

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


Timer1.Enabled = True

Try
With serialPort
.PortName = "COM12"
.BaudRate = 115200
.Parity = IO.Ports.Parity.None
.DataBits = 8
.StopBits = IO.Ports.StopBits.One
End With
serialPort.Open()

MsgBox(" connected.")
Try
serialPort.Write("ATE" + vbCr)


Catch ex As Exception
MsgBox(ex.ToString)
End Try


Catch ex As Exception
MsgBox(ex.ToString)
End Try


End Sub
-------------------------------------------------------------------------------------------------------------------


Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick

Try
serialPort.Write("AT+CMGL=""REC UNREAD""" & vbCr)

Catch ex As Exception
MsgBox(ex.ToString)
End Try


'MSComm1.Output = "AT+CMGL=""REC UNREAD""" & vbCr
End Sub
--------------------------------------------------------------------------------------------------------------------------------------------

Private Sub Decode(ByVal m As String)
Dim timestr As String
Dim datestr As String
Dim car_plate As String


i = InStr(1, m, """")
If (i > 0) Then
j = InStr(i + 1, m, """")
If (j > 0) Then
Handphone_number = Mid(m, i + 1, j - i - 1)
MsgBox("The Handphone number is:" + Handphone_number)


i = InStr(j + 1, m, """")
If (i > 0) Then
j = InStr(i + 1, m, """")
If (j > 0) Then
datetime = Mid(m, i + 1, j - i - 1)
datestr = Mid(datetime, 1, 8)
timestr = Mid(datetime, 10, 8)
MsgBox("The date is: " + datestr)
MsgBox("The time is: " + timestr)


car_plate = Mid(m, j + 1)
MsgBox("The car plate number is: " + car_plate)

End If
End If

End If
End If
End Sub
-------------------------------------------------------------------------------------------------------------------
Private Sub DataReceived( _
ByVal sender As Object, _
ByVal e As System.IO.Ports.SerialDataReceivedEventArgs) _
Handles serialPort.DataReceived

Dim somemore As Boolean

buffer = buffer + serialPort.ReadExisting
somemore = True
Do While somemore = True

i = InStr(buffer, HEADER)
If (i > 0) Then 'beginning of message found

j = InStr(i, buffer, EOM)
If (j > 0) Then 'end of message found

msg$ = Mid(buffer, i + Len(HEADER), j - i - Len(HEADER))
MsgBox(msg, vbInformation, "TEST")

Call Decode(msg$)
buffer = Mid(buffer, j + Len(EOM))
If Len(buffer) = 0 Then
somemore = False
End If

Else
somemore = False
End If
Else
somemore = False
End If
Loop

End Sub
-------------------------------------------------------------------------------------------------------------------
Private Sub executeDataCommand(ByVal s As String)
Dim cmd As OleDbCommand
Dim cn As New OleDbConnection(cnstr)

cn.Open()
cmd = New OleDbCommand(s, cn)
cmd.ExecuteNonQuery()
End Sub
-------------------------------------------------------------------------------------------------------------------

Private Function executeDataRead(ByVal s As String) As OleDbDataReader
Dim rd As OleDbDataReader
Dim cmd As OleDbCommand
Dim cn As New OleDbConnection(cnstr)

cn.Open()
cmd = New OleDbCommand(s, cn)

rd = cmd.ExecuteReader()
executeDataRead = rd
End Function

End Class
-------------------------------------------------------------------------------------------------------------------

if you decoded the data correctly then what is the problem in executing a simple INSERT query to insert your record into database.?? I cant understand your problem??? please explain the exact scenario...

[qwer]

if you decoded the data correctly then what is the problem in executing a simple INSERT query to insert your record into database.?? I cant understand your problem??? please explain the exact scenario...

For Query, may I know what statements do we have add in apart from the insert query?Im jus equipped with the basic knowledge about SQL statements and query.Can u show me some examples?
Thx

[Shashi Sadasivan]

what is your current sql insert statement that you are using?
We can build up on that

cheers

[qwer]

what is your current sql insert statement that you are using?
We can build up on that

cheers

hi currently does not have any SQL statements.Im actually trying to insert record into database in Microsoft Access.Told that should use insert query.However, im not sure of how and what query statement to add in.Can anyone help me on that.Thx in advance

[Shashi Sadasivan]

I see the followinf method which you have

Private Function executeDataRead(ByVal s As String) As OleDbDataReader

However there is no method calling this method.

s is your sql command

if you want to know more abt sql please check this site

cheers

[qwer]

May I know if I want to add in a INSERT INTO statement do I have to declare anything above in the VB.Net programme?example,

INSERT INTO Carpark_table (Carplate, date, TimeIn)
VALUES (car_plate, datestr, timestr)

when I debug the INSERT and VALUES are considered as errors where declarations are needed