MS Access DB Question

[Ventashar]

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

 

[Administrator]

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

 

[Ventashar]

x_x i don't understand that, where exactly does it accept the update in there?

 

[DavidT_macktool]

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]

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]

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

or just add the parameters.

 

[Ventashar]

Idk, it wasn't liking the Key, possibly because it was the db's primary key? *shrug* i got it working with a different command

 

[DavidT_macktool]

What is your command?

Want to share the command that works for you? Perhaps I could learn something to use in the future.

 

[TPM]

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]

I'll post it monday when i get back in school

 

[Ventashar]

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]

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]

What's the exact error?
Could be the permissions on your DB, you need to add asp-net user.

TPM

 

[bilro]

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 OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False")