Update data from a combobox that will show in a datagridview textbox

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 1388 Views - Last Post: 23 February 2015 - 09:07 AM Rate Topic: -----

#1 thadi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 19-February 15

Update data from a combobox that will show in a datagridview textbox

Posted 19 February 2015 - 11:23 AM

Hi. I am new in vb.net. I have MS Access Database that is binding with the vb.net front end. I get an error updating the data in the datagrid that is populating from a combobox which is pulling data from a table called Drivers where it has fields Dr_ID, Dr_LastName, Dr_FirstName. I have UsageDetail table with field U_ID, Dr_ID, NoComm. I am getting error on updating the Driver name in the datagrid. The error is saying "Data type mismatch in criteria expression". It's error on updating Driver data. The combobox is outside the datagrid I am trying to fix that. Anyone has any idea how do I start debugging it? Thank you.
Here is my code:

 
Private Sub btnEditC_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditC.Click

        Dim dbUSource As String = dbUseSource

        Dim myConnection As New Odbc.OdbcConnection(dbUSource)
        Dim myCommand As New Odbc.OdbcCommand(dbUSource, myConnection)

        Dim sqlUDet As String = "UPDATE UsageDetail SET "

        'Driver ID
        Dim strDrID As String = "Dr_ID = '" + cboDrID.Text + "', "
        sqlUDet = sqlUDet + strDrID

        'Number of Commutes
        Dim strNComm As String = "NoComm = '" + txtNoComm.Text + "' "
        sqlUDet = sqlUDet + strNComm

        myConnection.Open()

        myCommand.Connection = myConnection

        myCommand.CommandText = sqlUDet + " WHERE UD_ID = " + dgCom.CurrentRow.Cells(0).Value.ToString

        'myCommand.ExecuteNonQuery()
        'myConnection.Close()

        Try
            myCommand.ExecuteNonQuery()
            myConnection.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        'Update Data Grid
        Dim intDRow As Integer = dgCom.CurrentRow.Index

        dgCom.Item("U_ID", intDRow).Value = txtU_ID.Text
        dgCom.Item("Driver", intDRow).Value = cboDrID.Text
        dgCom.Item("Commutes", intDRow).Value = txtNoComm.Text

        CurrCommute()

        MsgBox("The Usage Detail has been updated!", MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "System Notification")

End Sub
 
Private Sub CurrCommute()

        Dim comRow As Integer = dgCom.CurrentRow.Index
        txtU_ID.Text = dgCom.Item("U_ID", comRow).Value.ToString
        cboDrID.Text = dgCom.Item("Driver", comRow).Value.ToString
        txtNoComm.Text = dgCom.Item("Commutes", comRow).Value.ToString

End Sub

    Private Sub loadCboDrID()

        Dim dbDriver As String
        Dim conDriver As New Odbc.OdbcConnection
        Dim strSQLDriver As String
        Dim intMaxUnits As Integer
        Dim dsDriver As New DataSet
        Dim daDriver As Odbc.OdbcDataAdapter

        'Clear out Driver List
        cboDrID.Items.Clear()

        dbDriver = strDBSource
        conDriver.ConnectionString = dbDriver

        conDriver.Open()

        strSQLDriver = "SELECT Dr_LastName, Dr_FirstName, Dr_ID  FROM Drivers ORDER BY Dr_LastName, Dr_FirstName "

        daDriver = New Odbc.OdbcDataAdapter(strSQLDriver, conDriver)
        daDriver.Fill(dsDriver, "Driver")

        conDriver.Close()

        intMaxUnits = dsDriver.Tables("Driver").Rows.Count

        For i = 0 To intMaxUnits - 1
            cboDrID.Items.Add(dsDriver.Tables("Driver").Rows(i).Item(0) & ", " & dsDriver.Tables("Driver").Rows(i).Item(1) & " (ID: " & dsDriver.Tables("Driver").Rows(i).Item(2) & ")")
        Next

    End Sub




Is This A Good Question/Topic? 0
  • +

Replies To: Update data from a combobox that will show in a datagridview textbox

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,276
  • Joined: 12-December 12

Re: Update data from a combobox that will show in a datagridview textbox

Posted 19 February 2015 - 11:33 AM

What line in your posted code produces the error?

Note that according to your description your UsageDetail table doesn't contain a field named UD_ID.



There is a debugging tutorial linked in my signature below.

This post has been edited by andrewsw: 19 February 2015 - 11:35 AM

Was This Post Helpful? 0
  • +
  • -

#3 thadi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 19-February 15

Re: Update data from a combobox that will show in a datagridview textbox

Posted 19 February 2015 - 11:40 AM

Thank for reply.
The following Code shows the error.
I missed mentioning that UD_ID is the primary key to the UsageDetail table.
        'Driver ID
        Dim strDrID As String = "Dr_ID = '" + cboDrID.Text + "', "
        sqlUDet = sqlUDet + strDrID

This post has been edited by andrewsw: 19 February 2015 - 11:42 AM
Reason for edit:: Removed previous quote, just press REPLY, and added code tags

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,276
  • Joined: 12-December 12

Re: Update data from a combobox that will show in a datagridview textbox

Posted 19 February 2015 - 11:46 AM

That code wouldn't produce the error "Data type mismatch in criteria expression". All that code is doing is constructing a string, although you should use the ampersand (&) to concatenate text, not the plus sign.

The error is more likely to happen at line 28. Is this the case? I would remove the Try..Catch block so that you can see the full error details.

Please confirm whether the field in the table is named UD_ID or U_ID?
Was This Post Helpful? 0
  • +
  • -

#5 thadi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 19-February 15

Re: Update data from a combobox that will show in a datagridview textbox

Posted 19 February 2015 - 12:20 PM

The same error showing in line 29 now after I removed the Try...Catch.

Yes, the Field is UD_ID which is the Primary key to the UsageDetail table. There is a U_ID also which is linked to Usage table with primary key U_ID. Usage and UsageDetail are linked by U_ID.
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,276
  • Joined: 12-December 12

Re: Update data from a combobox that will show in a datagridview textbox

Posted 19 February 2015 - 12:28 PM

Print out the value of this string:
myCommand.CommandText = sqlUDet + " WHERE UD_ID = " + dgCom.CurrentRow.Cells(0).Value.ToString

You might use
Debug.Print(sqlUDet + " WHERE UD_ID = " + dgCom.CurrentRow.Cells(0).Value.ToString)

just before this line. The text will appear in the Immediate or Output window so that you can copy and paste it here.

You should also use parameterized queries rather than concatenating strings. I have an example Snippet here. (It uses SQL Server but the principles are the same.) These are both secure and cause less issues with formatting/ data-type conversion.

This post has been edited by andrewsw: 19 February 2015 - 12:30 PM

Was This Post Helpful? 0
  • +
  • -

#7 thadi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 19-February 15

Re: Update data from a combobox that will show in a datagridview textbox

Posted 19 February 2015 - 12:44 PM

Do I add
Debug.Print(sqlUDet + " WHERE UD_ID = " + dgCom.CurrentRow.Cells(0).Value.ToString)

before the line
myCommand.CommandText = sqlUDet + " WHERE UD_ID = " + dgCom.CurrentRow.Cells(0).Value.ToString

I do not understand.
Was This Post Helpful? 0
  • +
  • -

#8 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,276
  • Joined: 12-December 12

Re: Update data from a combobox that will show in a datagridview textbox

Posted 19 February 2015 - 12:47 PM

yes
Was This Post Helpful? 0
  • +
  • -

#9 thadi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 19-February 15

Re: Update data from a combobox that will show in a datagridview textbox

Posted 19 February 2015 - 12:57 PM

View Postandrewsw, on 19 February 2015 - 12:47 PM, said:

yes

I will pickup from here tomorrow. Calling it a day. Thanks!
Was This Post Helpful? 0
  • +
  • -

#10 thadi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 19-February 15

Re: Update data from a combobox that will show in a datagridview textbox

Posted 20 February 2015 - 05:39 AM

I tried that and getting syntax error in Update statement.

This post has been edited by andrewsw: 20 February 2015 - 06:12 AM
Reason for edit:: Removed quotes, just press the REPLY button further down the page

Was This Post Helpful? 0
  • +
  • -

#11 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,276
  • Joined: 12-December 12

Re: Update data from a combobox that will show in a datagridview textbox

Posted 20 February 2015 - 06:11 AM

You will still get the error. A Debug.Print() statement isn't going to immediately fix the code. It sends text to the Immediate Window, or the Output Window, to help debug your code.

What text did it print? What does the SQL statement look like?
Was This Post Helpful? 0
  • +
  • -

#12 thadi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 19-February 15

Re: Update data from a combobox that will show in a datagridview textbox

Posted 20 February 2015 - 06:43 AM

I am attaching the error with the immediate window so you can see.
Thanks.
This is what immediate window shows with the error Data type mismatch in criteria expression in mycommand.ExecuteNonQuery.

at VCS1.My.MyApplication.Main(String[] Args)
at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>

sorry I didn't put the sql statement that shows on the Immediate window, here it is:

UPDATE UsageDetail SET Dr_ID = ' + cboDrID.Text + ', NoComm = ' + txtNoComm.Text + ' WHERE UD_ID = 817934
A first chance exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll
System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><TraceIdentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>VCS1.vshost.exe</AppDomain><Exception><ExceptionType>System.Data.Odbc.OdbcException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>ERROR [22018] [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.</Message><StackTrace> at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(Commandbehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(Commandbehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
Was This Post Helpful? 0
  • +
  • -

#13 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: Update data from a combobox that will show in a datagridview textbox

Posted 20 February 2015 - 06:59 AM

ok, this is all andrewsw is looking for

UPDATE UsageDetail SET Dr_ID = ' + cboDrID.Text + ', NoComm = ' + txtNoComm.Text + ' WHERE UD_ID = 817934

I haven't looked at the code but it appears you're not concatenating properly.

Upon glancing at your original code, It appears you've changed something since as the original code would have displayed the update statement with the actual values which is what andrewsw is wanting to see. However, I think you need to repost what you currently have that covers line 8 to line 22 of the block of code you posted above.

I'm going to bet you have a line that looks like this
Dim strDrID As String = "Dr_ID = ' + cboDrID.Text + ', "
Notice the two missing quotes that originally appeared in your first post

Which would be an incorrect way to concatenate a value from your control into the statement.

This post has been edited by CharlieMay: 20 February 2015 - 07:09 AM

Was This Post Helpful? 1
  • +
  • -

#14 thadi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 19-February 15

Re: Update data from a combobox that will show in a datagridview textbox

Posted 20 February 2015 - 09:35 AM

OK. I am posting the lines 8-24 again. I tried with single code and double code to test the data, but I get the same error.

Dim sqlUDet As String = "UPDATE UsageDetail SET "

        'Driver ID
        Dim strDrID As String = "Dr_ID = '" + cboDrID.Text + "', "
        'Dim strDrID As String = "Dr_ID = " + encodeDriver(cboDrID.Text) + ", "
        sqlUDet = sqlUDet + strDrID

        'Number of Commutes
        Dim strNComm As String = "NoComm = '" + txtNoComm.Text + "' "
        sqlUDet = sqlUDet + strNComm

        myConnection.Open()

        myCommand.Connection = myConnection

        Debug.Print(sqlUDet + " WHERE UD_ID = " + dgCom.CurrentRow.Cells(0).Value.ToString)

        myCommand.CommandText = sqlUDet + " WHERE UD_ID = " + dgCom.CurrentRow.Cells(0).Value.ToString
        MsgBox(myCommand.CommandText)

        myCommand.ExecuteNonQuery()
        myConnection.Close()


This post has been edited by andrewsw: 20 February 2015 - 09:46 AM
Reason for edit:: closed code tag with [/code]

Was This Post Helpful? 0
  • +
  • -

#15 thadi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 19-February 15

Re: Update data from a combobox that will show in a datagridview textbox

Posted 20 February 2015 - 09:48 AM

I would like to reword single code and double code with single quote and double quote.
Thanks.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2