MS Access DB Question

Ventashar

Member
Joined
Dec 7, 2004
Messages
13
Programming Experience
1-3
PrivateSub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim objCN AsNew ADODB.Connection

Dim strConnect AsString

Dim strSQL AsString

strConnect = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source=dtbMovies.mdb"

strSQL = "UPDATE Movie SET Movie = 'txtM1.text' , Time1 = 'txt11.text' WHERE Key = '1'"

objCN.Open(strConnect)

objCN.Execute(strSQL)

EndSub
visualbasic.net 2003 in case you couldn't tell
in the update statement, how do I make it realize that those are txt boxes on the form and not just straight text? thx
 
Last edited:

Administrator

VB.NET Forum Admin
Joined
Jun 3, 2004
Messages
1,461
Programming Experience
10+
Why aren't you using ADO.NET? You shouldn't be using ADO, but the new ADO.NET:

(Add Imports System.Data and System.Data.OleDb to the very top of your class)

Dim conn as new OLEDBConnection(connectionStringHere)
Dim cmd as new OleDbCommand(SQLStatementHere,conn)
cmd.CommandType = CommandType.Text

conn.open
cmd.ExecuteNonQuery()
conn.close

conn=nothing
cmd=nothing
 

DavidT_macktool

Well-known member
Joined
Oct 21, 2004
Messages
502
Location
Indiana
Programming Experience
3-5
Try using a variable

dim tmovie as string = txtM1.text
dim ttime as string = txt11.text

strSQL = "UPDATE Movie SET Movie = tmovie, Time1 = ttime WHERE Key = '1'"
 

Ventashar

Member
Joined
Dec 7, 2004
Messages
13
Programming Experience
1-3
DavidT_macktool said:
dim tmovie as string = txtM1.text
dim ttime as string = txt11.text

strSQL = "UPDATE Movie SET Movie = tmovie, Time1 = ttime WHERE Key = '1'"
doesn't work...says it's missing require parameters...i did this last year with some crazy thing surrounding the variable/object name...i just can't remember what it was, anyone wanna explain what neal was saying with that new ado thing?
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
strSQL = "UPDATE Movie SET Movie = '" & tmovie.text & "' , Time1 = '" & ttime.text & "' WHERE Key = '1'"

or just add the parameters.
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
Ventashar said:
Idk, it wasn't liking the Key, possibly because it was the db's primary key?
Really? Weird. I could see it not liking the table name and column name being the same, but the key...
 

Ventashar

Member
Joined
Dec 7, 2004
Messages
13
Programming Experience
1-3
strSQL = "UPDATE Movie SET Time5 = '" & txt55.Text & "' WHERE Time5 = '" & strArray(4, 5) & "'"

ended up using that for anyone that wanted to see it work
 

Victor

Member
Joined
Dec 14, 2004
Messages
7
Programming Experience
10+
Problem with using Update statement

Hello All,

I am trying to use simple update statement but I am keep getting this error message:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll


Following is the code I am trying to execute:

Dim strSQL as string

strSQL = "PROVIDER=Microsoft.Jet.oLEDB.4.0;Data " & "Source=C:\xxx\xxxx.mdb"
Dim conn As New OleDbConnection(strSQL)

Dim cmd As New OleDbCommand("Update table_name set column_name = test", conn)

cmd.CommandType = CommandType.Text

conn.Open()

cmd.ExecuteNonQuery()

conn.Close()

conn =
Nothing

cmd = Nothing


Could someone please tell me what am I doing wrong here?

Thanks.
 

TPM

Well-known member
Joined
Dec 7, 2004
Messages
623
Location
CA
Programming Experience
3-5
What's the exact error?
Could be the permissions on your DB, you need to add asp-net user.

TPM
 

bilro

Member
Joined
Jun 9, 2004
Messages
9
Programming Experience
3-5
Victor said:
Hello All,

I am trying to use simple update statement but I am keep getting this error message:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll


Following is the code I am trying to execute:

Dim strSQL as string

strSQL = "PROVIDER=Microsoft.Jet.oLEDB.4.0;Data " & "Source=C:\xxx\xxxx.mdb"
Dim conn As New OleDbConnection(strSQL)

Dim cmd As New OleDbCommand("Update table_name set column_name = test", conn)

cmd.CommandType = CommandType.Text

conn.Open()

cmd.ExecuteNonQuery()

conn.Close()

conn =
Nothing

cmd = Nothing


Could someone please tell me what am I doing wrong here?

Thanks.

try :
Dim conn As OleDbConnection = New OleDbConnection("
Data Source='C:\xxx\xxxx.mdb';Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider='Microsoft.Jet.OLEDB.4.0';Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False")
 
Top Bottom