Solved

Oledb   command text

Posted on 2013-06-07
9
328 Views
Last Modified: 2013-06-08
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
Comment
Question by:teogos
  • 4
  • 3
  • 2
9 Comments
 
LVL 25

Accepted Solution

by:
Ron M earned 500 total points
ID: 39229609
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
 

Author Comment

by:teogos
ID: 39229643
command.ExecuteNonQuery()  error here unclosed quotation mark after the character string
0
 
LVL 25

Expert Comment

by:Ron M
ID: 39229731
Sorry... I forgot a single quote
TXTCUT.TEXT & "' where
0
 

Author Comment

by:teogos
ID: 39229835
Error converting data type varchar to numeric    BOXES AND CUT ARE numeric on the database I can't change that
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39229879
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
 
LVL 25

Expert Comment

by:Ron M
ID: 39229883
Then you must have non-numeric characters in the textboxes?

You'll need to check for that in code.
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39229885
@xuserx2000

No, you need to remove the single quotes you added in the query from around the numeric values  ; )
0
 
LVL 25

Expert Comment

by:Ron M
ID: 39230077
ahhh... you're right sir.  But I agree with using the parameratized method though, which you posted.
0
 

Author Comment

by:teogos
ID: 39231855
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now