cannot update multiple columns with visual basic 2008

[lewelljo]

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.txtBad ge.Text" and thinking it's the column name. I have searched for days without any clue. Any help would be appreciated.
Thanks

    Sub chkGunIn()


        Dim daGun As New SqlDataAdapter
        Dim dsGun As New System.Data.DataSet
        Dim searchGunIn As String
        searchGunIn = DateTime.Now
        Dim mySelectQuery As String = ("UPDATE RF_Assign SET Gun_In = '" _
                                       & searchGunIn & "' ,Batt_In = '" & searchGunIn _
                                       & "' WHERE Badge = " & frmMain.txtBadge.Text)

        Dim myConnection As New SqlConnection(connectionString)
        Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
        Try

            myConnection.Open()
            daGun.SelectCommand = myCommand
            daGun.Fill(dsGun)
            myCommand.ExecuteNonQuery()
            myConnection.Close()
            cmdClearTXT()
            myConnection.Close()
        Catch ex As System.Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

[tlhintoq]

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.

[sashi]

     Sub chkGunIn(byVal strBadge as String)
         Dim daGun As New SqlDataAdapter
         Dim dsGun As New System.Data.DataSet
         Dim searchGunIn As String = ""
         Dim mySelectQuery As String = ""

         searchGunIn = DateTime.Now

         mySelectQuery = ""
         mySelectQuery = ("UPDATE RF_Assign SET Gun_In = '" _
                                        & searchGunIn & "' ,Batt_In = '" & searchGunIn _
                                        & "' WHERE Badge = " & Trim(strBadge))
   
          Dim myConnection As New SqlConnection(connectionString)
          Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
          Try
   
              myConnection.Open()
              daGun.SelectCommand = myCommand
              daGun.Fill(dsGun)
              myCommand.ExecuteNonQuery()

              myConnection.Close()
              cmdClearTXT()
              myConnection.Close()
          Catch ex As System.Exception
              MessageBox.Show(ex.Message)
          End Try
      End Sub

Call the sub as below;

      Call chkGunIn("insert_badge_details_here")

[lewelljo]

cannot update multiple columns with visual basic 2008

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.txtBad ge.text.

[sashi]

Are the column names correct?

Implement the below changes;

  Sub chkGunIn(byVal searchGunIn as string, byVal strBadge as String)

  Call chkGunIn("first_parameter","second_parameter")

[lewelljo]

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.txtBadg e.Text? I am so confused! I really thank you for your help.
This is what I have.....

  Call chkGunIn("Gun_In", "Batt_In")

And....

 Sub chkGunIn(ByVal searchGunIn As String, ByVal strBadge As String)


        Dim daGun As New SqlDataAdapter
        Dim dsGun As New System.Data.DataSet
        ' Dim searchGunIn As String = ""
        Dim mySelectQuery As String = ""

        searchGunIn = DateTime.Now

        mySelectQuery = ""
        mySelectQuery = ("UPDATE RF_Assign SET Gun_In = '" _
                                       & searchGunIn & "' ,Batt_In = '" & searchGunIn _
                                       & "' WHERE Badge = " & Trim(strBadge))

        Dim myConnection As New SqlConnection(connectionString)
        Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
        Try

            myConnection.Open()
            daGun.SelectCommand = myCommand
            daGun.Fill(dsGun)
            myCommand.ExecuteNonQuery()

            myConnection.Close()
            cmdClearTXT()
            myConnection.Close()
        Catch ex As System.Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

[lewelljo]

Yes the column names are correct. Here is the insert statement.

             insertStatement = "INSERT INTO RF_Assign (Autoi, Badge, Gun_Number, Gun_Out, Gun_In, Batt_Number, Batt_Out, Batt_In) VALUES ('" + Row_count + "','" _
            + frmMain.txtBadge.Text + "','" + frmMain.TxtGun.Text + _
            "','" + GOut + "','" + GIn + "','" + frmMain.txtBatt.Text + "','" + BOut + "','" + BIn + "')"

[lewelljo]

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

[tlhintoq]

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.txtBadg e.Text?

The only way your string "strBadge" is going to return a live result of the contents of "frmMain.textBa dge.Text is if strBadge is a property and the 'get' method gets that text.

// C#
string strBadge
{
    get
        {
              return frmMain.textBadge.Text;
        }
    set
        {
              frmMain.textBadge.Text = value;
        }
}

Constantly referring directly to the control on a form is fraught with problems, not the least of which is maintainability .

insertStatement = "INSERT INTO RF_Assign (Autoi, Badge, Gun_Number, Gun_Out, Gun_In, Batt_Number, Batt_Out, Batt_In) VALUES ('" + Row_count + "','" _
            + frmMain.txtBadge.Text + "','" + frmMain.TxtGun.Text + _
            "','" + 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.

[lewelljo]

I'm not familiar with C# at all and very novice with .net. I can make "strBadge" Public to = "frmMain.txtBad ge.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:

 Call chkGunIn("Gun_In", "Batt_In")

Sub chkGunIn(ByVal searchGunIn As String, ByVal strBadge As String)


        Dim daGun As New SqlDataAdapter
        Dim dsGun As New System.Data.DataSet
        ' Dim searchGunIn As String = ""
        Dim mySelectQuery As String = ""

        searchGunIn = DateTime.Now
        strBadge = frmMain.txtBadge.Text
        mySelectQuery = ""
        mySelectQuery = ("UPDATE RF_Assign SET Gun_In = '" _
                                       & searchGunIn & "' ,Batt_In = '" & searchGunIn _
                                       & "'WHERE Badge = " & strBadge)

        Dim myConnection As New SqlConnection(connectionString)
        Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
        Try

            myConnection.Open()
            daGun.SelectCommand = myCommand
            daGun.Fill(dsGun)
            myCommand.ExecuteNonQuery()

            myConnection.Close()
            cmdClearTXT()
            myConnection.Close()
        Catch ex As System.Exception
            MessageBox.Show(ex.Message)
        End Try
    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(dsG un)" 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.

[tlhintoq]

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.txtBadg e.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.

// C#
string strBadge
{
    get
        {
              return frmMain.textBadge.Text;
        }
    set
        {
              frmMain.textBadge.Text = value;
        }
}

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.

[lewelljo]

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 "mySelectQu ery" showing the correct string on break, but when trying to fill: daGun."Fill(dsG un)" it takes the contents of the string and beleives it to be a column?

[lewelljo]

Sorry for the delay but I got the answer a few days back.... Hope this helps others. The problem was in the query.

        mySelectQuery = ("UPDATE RF_Assign SET Gun_In = '" _
                                       & searchGunIn & "' ,Batt_In = '" & searchGunIn _
                                       & "' WHERE Badge = '" & strBadge & "'")

Thanks for your help.