Member Avatar for dru987

i have a mock site im working on in asp.net/vb.net using an access database. after employees log in they can update there employee profile by filling out a form. i need to know the code for updating an access DB with vb.net from the form fields. im lookin for the syntax on the SQL string and the execute commands afterwards. could someone help me with this or perhaps show me your code for some ideas to point me in the right direction?

also, what would be a good DB to use online? i know access well but i've heard that it doesnt work well online because only one person can access the DB at a time. any free DB's out there that i can scoop?

  • 4 Contributors
  • 6 Replies
  • 290 Views
  • 3 Years Discussion Span
  • Latest Post Latest Post by alc6379

Dani AI

Generated

Short primer and a working pattern for this thread (following ’s question and ’s reply): the correct SQL order is

UPDATE TableName SET col1 = value1, col2 = value2 WHERE condition

and an UPDATE should be run with ExecuteNonQuery() so the command executes and returns the number of affected rows.

' example using OleDb with parameters (Access .accdb)
Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\MyDB.accdb;"
Using conn As New OleDbConnection(connStr)
    conn.Open()
    Dim sql As String = "UPDATE Table1 SET usb = ?, cd = ?, [position] = ? WHERE cname = ? AND network = ?"
    Using cmd As New OleDbCommand(sql, conn)
        cmd.Parameters.Add("@usb", OleDbType.VarWChar).Value = txtusb.Text
        cmd.Parameters.Add("@cd", OleDbType.VarWChar).Value = txtcd.Text
        cmd.Parameters.Add("@position", OleDbType.VarWChar).Value = txtpos.Text   'wrap reserved names in []
        cmd.Parameters.Add("@cname", OleDbType.VarWChar).Value = txtcname.Text
        cmd.Parameters.Add("@network", OleDbType.VarWChar).Value = txtnet.Text
        Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
    End Using
End Using

Troubleshooting notes: the ORDER of SQL keywords matters (SET comes before WHERE) — that was the cause of the syntax error shown earlier. Prefer parameterized commands (shown above) to avoid quoting bugs and SQL injection. With OleDb and Access the parameter placeholders are positional (?), so add parameters in the same order as the placeholders. If a column name is a reserved word (e.g., position) wrap it in square brackets. Match parameter types for numbers/dates and handle Nulls explicitly.

About DB choice: Access is OK for small, intranet or single-user development but not ideal for public websites or many concurrent users. For hosted, free server-based options commonly used with ASP.NET include SQL Server Express (Windows), MySQL or PostgreSQL — they scale and avoid file-locking issues inherent to Access. For , fix the SET/WHERE order and use parameters; if problems persist, start a new thread with the exact SQL, field types and error text as suggested.

Recommended Answers

All 6 Replies

Member Avatar for tgreer

You're asking for a lot. You collect your form data from the properties of the various server controls. The syntax for SQL is the same regardless of the underlying database, more or less. MySQL is a good server-side database program.

If you are completely new to data-drive web development, then you need to break things down into discrete steps.

1) Connect to a database, retrieve information, display it on a page. That will teach you how to interact with your database.

2) Build a form with server controls. When the user submits a form, gather the information and post it back to the user. That will teach you how to gather form data.

3) Put it all together.

Member Avatar for dru987

prob should have been a little more clear with my question. i was just looking for the syntax for an UPDATE with sql (which if i just took 2 seconds i could find myself because i did :cheesy: ) the only question i have now is with a SELECT query i would run ExecuteReader() but this doesn't seem to be right with an update. is there another execute to use?

Member Avatar for tgreer

Oh. Yep, you use .ExecuteNonQuery().

Member Avatar for dru987

pimp, thanks again tgreer

Member Avatar for azaran2oo2

Hi,
I have a form in which i want to update data if there is a repeated data in tne table. the table is in access, and i use: vb.net2003.
but i recieve the syntax error in update command. will you please help me:

objCmd = New OleDbCommand("UPDATE Table1 WHERE (cname=" & txtcname.Text & " AND network=" & txtnet.Text & ")) set(usb=" & txtusb.Text & ",cd=" & txtcd.Text & ",position=" & txtpos.Text & ",", New OleDbConnection(cmd))

i put cname and network as key in the table.
thx

Member Avatar for alc6379

Hi,
I have a form in which i want to update data if there is a repeated data in tne table. the table is in access, and i use: vb.net2003.
but i recieve the syntax error in update command. will you please help me:
objCmd = New OleDbCommand("UPDATE Table1 WHERE (cname=" & txtcname.Text & " AND network=" & txtnet.Text & ")) set(usb=" & txtusb.Text & ",cd=" & txtcd.Text & ",position=" & txtpos.Text & ",", New OleDbConnection(cmd))

i put cname and network as key in the table.
thx

Hi,

Start your own thread with this question. That way, you'll get the information that you require, and you won't muddle up someone else's thread with a question that might not be directly related...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.