[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

Oledb command text

Visual Studio 2010  Vb.net

I want to write these values to the database

using this  but I am not sure of the sintax

cn = New OleDb.OleDbConnection
        cn.ConnectionString = (My.Settings.cnStringAdo1)
        command = New OleDb.OleDbCommand
        command.Connection = cn
        command.Connection.Open()
        command.CommandText = ("UPDATE [3320Monitor].MASTERSCHEDULE (BOXES,CUT,CODE) where CODE='" & txtcode.Text + "'")
        command.ExecuteNonQuery()
        command.Connection.Close()

I want to overwrite the records  based on teh TXTCODE.TEXT

The fields I want to update BOXES, CUT, CODE   their values come from a TXTBOXES.TEXT and TXTCUT.TEXT and TXTCODE.TeXT


AGAIN is WINDOWS FORMS 2010 VB
0
teogos
Asked:
teogos
  • 4
  • 3
  • 2
1 Solution
 
Ron MalmsteadInformation Services ManagerCommented:
command.CommandText = "UPDATE [3320Monitor].MASTERSCHEDULE SET BOXES='" & TXTBOXES.TEXT & "',CUT='" & TXTCUT.TEXT & " where CODE='" & txtcode.Text & "'"

..also, if you do

Dim RecordsCount as integer = command.ExecuteNonQuery()

You'll get the number of records affected.
0
 
teogosAuthor Commented:
command.ExecuteNonQuery()  error here unclosed quotation mark after the character string
0
 
Ron MalmsteadInformation Services ManagerCommented:
Sorry... I forgot a single quote
TXTCUT.TEXT & "' where
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
teogosAuthor Commented:
Error converting data type varchar to numeric    BOXES AND CUT ARE numeric on the database I can't change that
0
 
käµfm³d 👽Commented:
You would be better served if you used parameterized queries.

e.g.

command.CommandText = ("UPDATE [3320Monitor].MASTERSCHEDULE SET BOXES = ?, CUT = ? where CODE= ?")
command.Parameters.AddWithValue("?", Convert.ToInt32(TXTBOXES.TEXT))
command.Parameters.AddWithValue("?", Convert.ToInt32(TXTCUT.TEXT))
command.Parameters.AddWithValue("?", Convert.ToInt32(TXTCODE.TeXT))

Open in new window


There's no error handling in the above, so if either text box does not contain a valid integer, then you will encounter an exception. You would want to account for this, of course. Also, it's not clear from my example above, but the order that you add the parameters to the command object must be the same as how they appear in the query (for future reference).
0
 
Ron MalmsteadInformation Services ManagerCommented:
Then you must have non-numeric characters in the textboxes?

You'll need to check for that in code.
0
 
käµfm³d 👽Commented:
@xuserx2000

No, you need to remove the single quotes you added in the query from around the numeric values  ; )
0
 
Ron MalmsteadInformation Services ManagerCommented:
ahhh... you're right sir.  But I agree with using the parameratized method though, which you posted.
0
 
teogosAuthor Commented:
I got to it work using this
command.CommandText = "UPDATE [3320Monitor].MASTERSCHEDULE SET BOXES='" & Val(txtboxes.Text) & "',CUT='" & Val(txtcut.Text) & "' where CODE='" & txtcode.Text & "'"
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now