473,320 Members | 1,879 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

cannot update multiple columns with visual basic 2008

Hello Everyone & please excuse my ignorance. I am fairley new to .net and newer to SQL. The below code gives me an exception telling me that it is an invalid column name. It's taking the input string from "frmMain.txtBadge.Text" and thinking it's the column name. I have searched for days without any clue. Any help would be appreciated.
Thanks


Expand|Select|Wrap|Line Numbers
  1.     Sub chkGunIn()
  2.  
  3.  
  4.         Dim daGun As New SqlDataAdapter
  5.         Dim dsGun As New System.Data.DataSet
  6.         Dim searchGunIn As String
  7.         searchGunIn = DateTime.Now
  8.         Dim mySelectQuery As String = ("UPDATE RF_Assign SET Gun_In = '" _
  9.                                        & searchGunIn & "' ,Batt_In = '" & searchGunIn _
  10.                                        & "' WHERE Badge = " & frmMain.txtBadge.Text)
  11.  
  12.         Dim myConnection As New SqlConnection(connectionString)
  13.         Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
  14.         Try
  15.  
  16.             myConnection.Open()
  17.             daGun.SelectCommand = myCommand
  18.             daGun.Fill(dsGun)
  19.             myCommand.ExecuteNonQuery()
  20.             myConnection.Close()
  21.             cmdClearTXT()
  22.             myConnection.Close()
  23.         Catch ex As System.Exception
  24.             MessageBox.Show(ex.Message)
  25.         End Try
  26.     End Sub
Feb 14 '10 #1
12 3041
tlhintoq
3,525 Expert 2GB
TIP: When you are writing your question, there is a button on the tool bar that wraps the [code] tags around your copy/pasted code. It helps a bunch. Its the button with a '#' on it. More on tags. They're cool. Check'em out.
Feb 14 '10 #2
sashi
1,754 Expert 1GB
Expand|Select|Wrap|Line Numbers
  1.      Sub chkGunIn(byVal strBadge as String)
  2.          Dim daGun As New SqlDataAdapter
  3.          Dim dsGun As New System.Data.DataSet
  4.          Dim searchGunIn As String = ""
  5.          Dim mySelectQuery As String = ""
  6.  
  7.          searchGunIn = DateTime.Now
  8.  
  9.          mySelectQuery = ""
  10.          mySelectQuery = ("UPDATE RF_Assign SET Gun_In = '" _
  11.                                         & searchGunIn & "' ,Batt_In = '" & searchGunIn _
  12.                                         & "' WHERE Badge = " & Trim(strBadge))
  13.  
  14.           Dim myConnection As New SqlConnection(connectionString)
  15.           Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
  16.           Try
  17.  
  18.               myConnection.Open()
  19.               daGun.SelectCommand = myCommand
  20.               daGun.Fill(dsGun)
  21.               myCommand.ExecuteNonQuery()
  22.  
  23.               myConnection.Close()
  24.               cmdClearTXT()
  25.               myConnection.Close()
  26.           Catch ex As System.Exception
  27.               MessageBox.Show(ex.Message)
  28.           End Try
  29.       End Sub
  30.  
Call the sub as below;
Expand|Select|Wrap|Line Numbers
  1.       Call chkGunIn("insert_badge_details_here")
  2.  
Feb 15 '10 #3
Thanks Sashi for the response. I copied and pasted your code and am still getting the same error... Invalid column name 'TNJCL'. TNJCL is the string content of "frmMain.txtBadge.text.
Feb 15 '10 #4
sashi
1,754 Expert 1GB
Are the column names correct?

Implement the below changes;
Expand|Select|Wrap|Line Numbers
  1.   Sub chkGunIn(byVal searchGunIn as string, byVal strBadge as String)
  2.  
Expand|Select|Wrap|Line Numbers
  1.   Call chkGunIn("first_parameter","second_parameter")
  2.  
Feb 15 '10 #5
Thanks Sashi. There still is a problem. It runs without an exception but will not update. When I check it at the break the " Trim(strBadge)" is "Batt_In" Should I not be looking for the contents of frmMain.txtBadge.Text? I am so confused! I really thank you for your help.
This is what I have.....
Expand|Select|Wrap|Line Numbers
  1.   Call chkGunIn("Gun_In", "Batt_In")   
And....

Expand|Select|Wrap|Line Numbers
  1.  Sub chkGunIn(ByVal searchGunIn As String, ByVal strBadge As String)
  2.  
  3.  
  4.         Dim daGun As New SqlDataAdapter
  5.         Dim dsGun As New System.Data.DataSet
  6.         ' Dim searchGunIn As String = ""
  7.         Dim mySelectQuery As String = ""
  8.  
  9.         searchGunIn = DateTime.Now
  10.  
  11.         mySelectQuery = ""
  12.         mySelectQuery = ("UPDATE RF_Assign SET Gun_In = '" _
  13.                                        & searchGunIn & "' ,Batt_In = '" & searchGunIn _
  14.                                        & "' WHERE Badge = " & Trim(strBadge))
  15.  
  16.         Dim myConnection As New SqlConnection(connectionString)
  17.         Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
  18.         Try
  19.  
  20.             myConnection.Open()
  21.             daGun.SelectCommand = myCommand
  22.             daGun.Fill(dsGun)
  23.             myCommand.ExecuteNonQuery()
  24.  
  25.             myConnection.Close()
  26.             cmdClearTXT()
  27.             myConnection.Close()
  28.         Catch ex As System.Exception
  29.             MessageBox.Show(ex.Message)
  30.         End Try
  31.     End Sub
Feb 15 '10 #6
Yes the column names are correct. Here is the insert statement.
Expand|Select|Wrap|Line Numbers
  1.              insertStatement = "INSERT INTO RF_Assign (Autoi, Badge, Gun_Number, Gun_Out, Gun_In, Batt_Number, Batt_Out, Batt_In) VALUES ('" + Row_count + "','" _
  2.             + frmMain.txtBadge.Text + "','" + frmMain.TxtGun.Text + _
  3.             "','" + GOut + "','" + GIn + "','" + frmMain.txtBatt.Text + "','" + BOut + "','" + BIn + "')"
  4.  
Feb 15 '10 #7
This is the contents of mySelectQuery =
UPDATE RF_Assign SET Gun_In = '2/15/2010 9:53:58 AM' ,Batt_In = '2/15/2010 9:53:58 AM' WHERE Badge = Batt_In
Feb 15 '10 #8
tlhintoq
3,525 Expert 2GB
Pardon me for barging in...
When I check it at the break the " Trim(strBadge)" is "Batt_In" Should I not be looking for the contents of frmMain.txtBadge.Text?
The only way your string "strBadge" is going to return a live result of the contents of "frmMain.textBadge.Text is if strBadge is a property and the 'get' method gets that text.

Expand|Select|Wrap|Line Numbers
  1. // C#
  2. string strBadge
  3. {
  4.     get
  5.         {
  6.               return frmMain.textBadge.Text;
  7.         }
  8.     set
  9.         {
  10.               frmMain.textBadge.Text = value;
  11.         }
  12. }
Constantly referring directly to the control on a form is fraught with problems, not the least of which is maintainability.
Expand|Select|Wrap|Line Numbers
  1. insertStatement = "INSERT INTO RF_Assign (Autoi, Badge, Gun_Number, Gun_Out, Gun_In, Batt_Number, Batt_Out, Batt_In) VALUES ('" + Row_count + "','" _
  2.             + frmMain.txtBadge.Text + "','" + frmMain.TxtGun.Text + _
  3.             "','" + GOut + "','" + GIn + "','" + frmMain.txtBatt.Text + "','" + BOut + "','" + BIn + "')"
If you have 100 queries like this and you make a change to the form then you have to update 100 places. If you use a property then you only have to change 1.
Feb 15 '10 #9
I'm not familiar with C# at all and very novice with .net. I can make "strBadge" Public to = "frmMain.txtBadge.text". I understand that is just good practice and will keep it in mind when cleaning this up. So here's the code as of right now:
Expand|Select|Wrap|Line Numbers
  1.  Call chkGunIn("Gun_In", "Batt_In") 
Expand|Select|Wrap|Line Numbers
  1. Sub chkGunIn(ByVal searchGunIn As String, ByVal strBadge As String)
  2.  
  3.  
  4.         Dim daGun As New SqlDataAdapter
  5.         Dim dsGun As New System.Data.DataSet
  6.         ' Dim searchGunIn As String = ""
  7.         Dim mySelectQuery As String = ""
  8.  
  9.         searchGunIn = DateTime.Now
  10.         strBadge = frmMain.txtBadge.Text
  11.         mySelectQuery = ""
  12.         mySelectQuery = ("UPDATE RF_Assign SET Gun_In = '" _
  13.                                        & searchGunIn & "' ,Batt_In = '" & searchGunIn _
  14.                                        & "'WHERE Badge = " & strBadge)
  15.  
  16.         Dim myConnection As New SqlConnection(connectionString)
  17.         Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
  18.         Try
  19.  
  20.             myConnection.Open()
  21.             daGun.SelectCommand = myCommand
  22.             daGun.Fill(dsGun)
  23.             myCommand.ExecuteNonQuery()
  24.  
  25.             myConnection.Close()
  26.             cmdClearTXT()
  27.             myConnection.Close()
  28.         Catch ex As System.Exception
  29.             MessageBox.Show(ex.Message)
  30.         End Try
  31.     End Sub 
mySelectQuery Returns the proper string on BREAK: "UPDATE RF_Assign SET Gun_In = '2/15/2010 1:15:40 PM' ,Batt_In = '2/15/2010 1:15:40 PM'WHERE Badge = TN1002" But when gets to the daGun."Fill(dsGun)" it goes to the exception with: Invalid column name 'TN1002'.

Does any of this make sense? I am still at a loss as to what is causing this problem. I had the same thing happen in another part of the code and it was the Query statement causing the issue. I just cannot figure this out.
Feb 15 '10 #10
tlhintoq
3,525 Expert 2GB
Expand|Select|Wrap|Line Numbers
  1. strBadge = frmMain.txtBadge.Text
No.

This will ONE TIME at the time the code is run set strBadge equal to the current value of frmMain.txtBadge.Text

It will not get the real-time updated value of the text box each time you use the strBadge variable. That is what a property does as I described earlier.
Expand|Select|Wrap|Line Numbers
  1. // C#
  2. string strBadge
  3. {
  4.     get
  5.         {
  6.               return frmMain.textBadge.Text;
  7.         }
  8.     set
  9.         {
  10.               frmMain.textBadge.Text = value;
  11.         }
  12. }
In this example every time you use strBadge the 'get' method is executed and retreives the CURRENT value of the textbox. When you set strBadge to a new value, that value is inserted into the .Text property of the textbox

I recommend you go to the MSDN to read up on properties to get a better understanding of what I tried to describe.
Feb 15 '10 #11
Thanks tl. I understand what you are saying and in my hast in replying I did not state it clearly. But, as it is above, should this not work? Why is "mySelectQuery" showing the correct string on break, but when trying to fill: daGun."Fill(dsGun)" it takes the contents of the string and beleives it to be a column?
Feb 15 '10 #12
Sorry for the delay but I got the answer a few days back.... Hope this helps others. The problem was in the query.

Expand|Select|Wrap|Line Numbers
  1.         mySelectQuery = ("UPDATE RF_Assign SET Gun_In = '" _
  2.                                        & searchGunIn & "' ,Batt_In = '" & searchGunIn _
  3.                                        & "' WHERE Badge = '" & strBadge & "'")
  4.  
Thanks for your help.
Feb 21 '10 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: David Londeck | last post by:
I am using Visual Basic 2003 and I have written a notepad like application using the RichTextBox control. I am having trouble trying to emulate Microsoft Words text block copy/paste feature. In...
6
by: Aaron Smith | last post by:
Ok. I have a dataset that has multiple tables in it. In one of the child tables, I have a column that I added to the DataSet (Not in the DataSource). This column does not need to be stored in the...
5
by: mark | last post by:
I've been looking at working with Excel data. I understand the process of getting the data into a dataset and modifying it. It's one of simple beauty that is well documented. Now, I want to send...
4
by: papa smerf | last post by:
I have a list box that I want to have 3 columns of data. For example: FirstName, LastName, Phone, Catagory I want to display the FirstName, LastName and Phone in the 3 columns, I will use...
6
by: Bill Nguyen | last post by:
I tried almost everything ..Rows.clear() ..rowcount = 0 and the rows in a datagridview still not cleared I populated the Datagridview manually using Row.Add Any help is greatly appreciated ...
0
by: yukijocelyn | last post by:
I have experienced a problem here while doing a form for accessing datadbase using MS Access. I'm using the AccessDataSource control, and using Gridview to pull the data from the database. I am able...
1
by: Hetal | last post by:
Hi... We are on Visual Basic .NET 2003 and i am using DataView to sort the DataTable. However, we are facing problems with sorting the data based on multiple columns. The below statement to...
1
by: radhikabista | last post by:
hey friends , i m not being able to save the updates in datagrid view when i press buttonsave_ gridview i have a class customer with two methods one to get the dataset and other to update database:...
1
by: =?Utf-8?B?Rmxhbm1hbg==?= | last post by:
I have a tutorial I was working on with Visual Web Developer 2005 express. Very basic stuff intro to asp.net. All was working fine a few days ago. I took my pc home and installed the visual studion...
3
by: Michel Esber | last post by:
Hi all, DB2 V8 LUW FP 15 There is a table T (ID varchar (24), ABC timestamp). ID is PK. Our application needs to frequently update T with a new value for ABC. update T set ABC=? where ID...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.