fail to insert into access..

[shuhana84]

Function selectDB17()

Dim cn As New SqlConnection
Dim cmd As New SqlCommand

cn.ConnectionString = ("server=EKS-HQ;database=BGB;user id=administrator;password=admineks2005")
cn.Open()
cmd.Connection = cn
cmd.CommandText = "SELECT KODSTATE,USERID,PASSWORD,KUMPULAN FROM USERID"
Dim lrd As SqlDataReader = cmd.ExecuteReader()
Try
Do While lrd.Read()
If lrd.HasRows Then
a = lrd("KODSTATE")
b = lrd("USERID")
c = lrd("PASSWORD")
d = lrd("KUMPULAN")

Else
MsgBox("Tiada Data")
End If
insertDB17()
Loop
Catch ex As Exception
MessageBox.Show("Error while retrieving records on table..." & ex.Message, "Load Records")
Finally
cn.Close()
End Try

End Function

Function insertDB17()
Dim con As OleDb.OleDbConnection
Dim str As String
Dim cmd As OleDb.OleDbCommand
Try

con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;data source=" & strsaveDB & "\" & strnameDB & ".mdb")
con.Open()
str = "insert into USERID(KODSTATE,USERID,PASSWORD,KUMPULAN)values('" & a & "' ,'" & b & "','" & c & "','" & d & "')"
cmd = New OleDb.OleDbCommand(str, con)
cmd.ExecuteNonQuery()
Catch
End Try
con.Close()

End Function

=================================================
hi..there..did anyone know what wrong with my code..i've fail to insert in table USERID in access..actually,i've use the same code to insert before this and it succeed..

[hirenpatel53]

str = "insert into USERID(KODSTATE,USERID,PASSWORD,KUMPULAN)values('" & a & "' ," & b & ",'" & c & "','" & d & "')"

try this will work eor you

if work please dont forgate to mark as solved

[hirenpatel53]

ypur USERID must be integer and for insertin integer there is no need of Single quates(') try and confom

[shuhana84]

Permalink 1 Minute Ago
0 Re: fail to insert into access..VB.NET Syntax (Toggle Plain Text)
str = "insert into USERID(KODSTATE,USERID,PASSWORD,KUMPULAN)values('" & a & "' ," & b & ",'" & c & "','" & d & "')"str = "insert into USERID(KODSTATE,USERID,PASSWORD,KUMPULAN)values('" & a & "' ," & b & ",'" & c & "','" & d & "')"
try this will work eor you

if work please dont forgate to mark as solved

i don't see the different btween your code n mine...it look same for me...(T_T)

[shuhana84]

str = "insert into USERID(KODSTATE,USERID,PASSWORD,KUMPULAN)values('" & a & "' ," & b & ",'" & c & "','" & d & "')"

try this will work eor you

if work please dont forgate to mark as solved

i don't see the different btween your code n mine...it look same for me...(T_T)

[shuhana84]

ypur USERID must be integer and for insertin integer there is no need of Single quates(') try and confom

ooo i see..i'll try this..tq..

[hirenpatel53]

Your Link

str = "insert into USERID(KODSTATE,USERID,PASSWORD,KUMPULAN)values('" & a & "' ,'" & b & "','" & c & "','" & d & "')"

my link dear

str = "insert into USERID(KODSTATE,USERID,PASSWORD,KUMPULAN)values('" & a & "' ," & b & ",'" & c & "','" & d &  "')"str = "insert into USERID(KODSTATE,USERID,PASSWORD,KUMPULAN)values('" & a & "' ," & b & ",'" & c & "','" & d & "')"

[shuhana84]

ypur USERID must be integer and for insertin integer there is no need of Single quates(') try and confom

i've test this,but it still didn't work..actually my USERID not in integer but in "[USERID] nvarchar"

[hirenpatel53]

if its nvarchar then you code is ok and check it is primary key or not if primary then check you are not reapiting value

and next time post your error and use your code ok

[shuhana84]

if its nvarchar then you code is ok and check it is primary key or not if primary then check you are not reapiting value

and next time post your error and use your code ok

this code run well,and don't have error...but after finish the process the USERID table still emtpy..

[pritesh2010]

ok then try to insert the with transaction. first add the refrence of transaction and then pass the query with that see this link Transaction

[hirenpatel53]

try in debug check the value of b on each step and while reaches to

str = "insert into USERID(KODSTATE,USERID,PASSWORD,KUMPULAN)values('" & a & "' ,'" & b & "','" & c & "','" & d & "')"

copy the query from there and execute that query in your sql server mngmnt studio and then check and examine query and same post here

[hirenpatel53]

useebug and chech value of b and retrive query from str and run in your sql mngmnt studio then check is there any error

[hirenpatel53]

use debug map the value of b every time and copy query from str and execute it into your sql mngmnt studio and look for error if value coming then will definitly insert into table

[hirenpatel53]

sorry for posting same solution done by bymistake realy sorry for that

[G_Waddell]

Hi,

Does the user you are connecting to the access DB with have write permissions on the file / directory? The code looks ok but if the table is empty something is stopping you.

[jugosoft]

How can you insert/read data from Access database with SqlConnection, SqlCommand and SqlDataReader???
Declare connection OleDb or ODBC.Namespace is System.Data.Odbc/OleDb.

[shuhana84]

if its nvarchar then you code is ok and check it is primary key or not if primary then check you are not reapiting value

and next time post your error and use your code ok

when i remove try/catch there is an error in insertDB17
"error while retrieving record......

[shuhana84]

try in debug check the value of b on each step and while reaches to

str = "insert into USERID(KODSTATE,USERID,PASSWORD,KUMPULAN)values('" & a & "' ,'" & b & "','" & c & "','" & d & "')"

copy the query from there and execute that query in your sql server mngmnt studio and then check and examine query and same post here

i've i try to debug and of coz the a,b,c,d have different value at every loop

[shuhana84]

How can you insert/read data from Access database with SqlConnection, SqlCommand and SqlDataReader???
Declare connection OleDb or ODBC.Namespace is System.Data.Odbc/OleDb.

actually i select data from sql server then insert in access...

[pritesh2010]

see this link for how to update access database same way you can insert the data like thathere

[pritesh2010]

put try catch block and see what is the error.:)