locked
data type problem SQLEmail = "UPdate [Payments] set [AmountPaid] = '20', [Registration_ID]='11', [DatePaid]= '9/12/2016', [AmountChips]='3', [AmountRegistration]='2' where [PaymentID]=27 " in SQL update query RRS feed

  • Question

  • User224181609 posted

    I have an update query that works fine if I put in actual values, for example, a specific date.  as below:

     SQLEmail = "UPdate [Payments] set [AmountPaid] = '20', [Registration_ID]='11', [DatePaid]= '9/12/2016', [AmountChips]='3', [AmountRegistration]='2' where [PaymentID]=27 "
    

    However if I replace '9/12/2016' with 'date.now' , I get the error "Data type mismatch in criteria expression"

    The same thing happens if I replace the '20'  for the [AmountPaid] value with 'LblAmount.text' 

    How do I manage to get values from controls into this SQL statement?

    Sunday, September 11, 2016 12:09 PM

Answers

  • User283571144 posted

    Hi ClarkNK,

    This code returns a Data Type Mismatch error that has to do with the parameter @AmountPaid , because if I just replace the @AmountPaid in the update query with a number like 25. and comment-out the two lines just above the ExecuteNonQuery statement, the update query works just fine.

    I'm hoping someone can spot what is wrong that is causing the data mismatch involving the AmountPaid field

    As far as I know, this error indicates that Access cannot match an input value to the data type it expects for the value.

    For example, if you give Access a text string when it is expecting a number, you receive a data type mismatch error.

    So I suggest you could try below codes:

    cmd.Parameters.Add("@AmountPaid", OleDbType.Numeric, 8).Value = MyAmount

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 12, 2016 2:04 AM
  • User283571144 posted

    Hi ClarkNK,

    According to your previous posts codes, I find you add "'" around the "@DatePaid". I suggest you could delete it.

    Like below:

    SQLEmail = "UPdate [DateTimeTest] set [Datetime] = @DatePaid where [ID]=1  "

    Besides, I had written a test demo on my computer, it works well.

    More details, you could refer to follow codes:

      Dim Con As New OleDb.OleDbConnection
    
            Dim cmd As New OleDb.OleDbCommand
    
            Dim SQLEmail As String
    
            Con.ConnectionString = "PROVIDER = Microsoft.Jet.OLEDB.4.0; Data Source = |DataDirectory|Database1.mdb"
    
            Con.Open()
    
            SQLEmail = "UPdate [DateTimeTest] set [Datetime] = @DatePaid where [ID]=1  "
    
            cmd = New OleDb.OleDbCommand(SQLEmail, Con)
    
            cmd.Parameters.Add("@DatePaid", OleDb.OleDbType.DBDate).Value = Date.Now
    
            cmd.ExecuteNonQuery()
    
            Con.Close()

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 13, 2016 4:31 AM

All replies

  • User283571144 posted

    Hi ClarkNK,

    ClarkNK

    However if I replace '9/12/2016' with 'date.now' , I get the error "Data type mismatch in criteria expression"

    The same thing happens if I replace the '20'  for the [AmountPaid] value with 'LblAmount.text' 

    How do I manage to get values from controls into this SQL statement?

    According to your description, I suggest you could use SqlCommand.Parameters Property to manage to get values from controls into this SQL statement.

    From MSDN:

    The Microsoft .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to a SQL Statement or a stored procedure called by a command of CommandType.Text. In this case, named parameters must be used. 

    Note:

    If the parameters in the collection do not match the requirements of the query to be executed, an error may result.

    For example, in SQL column's type is DateTime, but in ADO.NET you set the parameter's type is string, an error may result.

    For more information, see Configuring Parameters and Parameter Data Types.

    More details, you could refer to follow codes and link:

    DateTime date = DateTime.Now;
                string SQLEmail = "UPdate [Payments] set [AmountPaid] = @userid, [Registration_ID]='11', [DatePaid]= @date, [AmountChips]='3', [AmountRegistration]='2' where [PaymentID]=27 ";
                using (SqlCommand cmd = new SqlCommand(SQLEmail, conn))
                {
                    cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = date;
                    cmd.Parameters.Add("@userid", SqlDbType.VarChar, 20).Vaule = LblAmount.text;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }

    Link:https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx

    Best Regards,

    Brando

    Sunday, September 11, 2016 12:47 PM
  • User224181609 posted

    Brando, thank you very much for your reply, it has been very helpful to my understanding. I think I am getting close, but still am not there. So I am going to provide more details here in hopes of learning what thing I am missing.  

    I am using an Access database, do things are a little different from, but similar to the material you provided.

    First, here is a screenshot of the field definitions (Note particularly the AmountPaid field is a Number/Integer)

    I am providing below the code I am trying:

     Dim Con As New OleDb.OleDbConnection
    
            Dim cmd As New OleDb.OleDbCommand
    
            Dim SQLEmail As String
    
            Con.ConnectionString = "PROVIDER = Microsoft.Jet.OLEDB.4.0; Data Source = |DataDirectory|Conference2017Registration.mdb"
    
            Con.Open()
            
            SQLEmail = "UPdate [Payments] set [AmountPaid] = '@AmountPaid', [Registration_ID]='11', [DatePaid]= '5/5/2016', [AmountChips]='3', [AmountRegistration]='2' where [PaymentID]=27 "
    
            cmd = New OleDb.OleDbCommand(SQLEmail, Con)
    
            Dim MyAmount As Integer = LblAmount.Text
    
            cmd.Parameters.AddWithValue("@AmountPaid", MyAmount)
    
            cmd.ExecuteNonQuery()
    
            Con.Close()

    This code returns a Data Type Mismatch error that has to do with the parameter @AmountPaid , because if I just replace the @AmountPaid in the update query with a number like 25. and comment-out the two lines just above the ExecuteNonQuery statement, the update query works just fine.

    I'm hoping someone can spot what is wrong that is causing the data mismatch involving the AmountPaid field

    Monday, September 12, 2016 12:59 AM
  • User283571144 posted

    Hi ClarkNK,

    This code returns a Data Type Mismatch error that has to do with the parameter @AmountPaid , because if I just replace the @AmountPaid in the update query with a number like 25. and comment-out the two lines just above the ExecuteNonQuery statement, the update query works just fine.

    I'm hoping someone can spot what is wrong that is causing the data mismatch involving the AmountPaid field

    As far as I know, this error indicates that Access cannot match an input value to the data type it expects for the value.

    For example, if you give Access a text string when it is expecting a number, you receive a data type mismatch error.

    So I suggest you could try below codes:

    cmd.Parameters.Add("@AmountPaid", OleDbType.Numeric, 8).Value = MyAmount

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 12, 2016 2:04 AM
  • User224181609 posted

    Brando, you have been a great assistance, that was just what was needed.  I will be marking your posts as the answer, but first I have one more question.

    The only remaining snag is adding the date parameter. I have tried all of the following without success:

     cmd.Parameters.Add("@DatePaid", OleDb.OleDbType.Date).Value = Date.Now
    
            cmd.Parameters.Add("@DatePaid", OleDb.OleDbType.DBDate).Value = Date.Now
    
            cmd.Parameters.Add("@DatePaid", OleDb.OleDbType.DBTimeStamp).Value = Date.Now

    I really thought the first one would work, but no joy :=(

    The last one I did not expect to work, and it did not.  Any idea?

    Thank you very much for your assistance with this.

    Monday, September 12, 2016 12:27 PM
  • User283571144 posted

    Hi ClarkNK,

    According to your previous posts codes, I find you add "'" around the "@DatePaid". I suggest you could delete it.

    Like below:

    SQLEmail = "UPdate [DateTimeTest] set [Datetime] = @DatePaid where [ID]=1  "

    Besides, I had written a test demo on my computer, it works well.

    More details, you could refer to follow codes:

      Dim Con As New OleDb.OleDbConnection
    
            Dim cmd As New OleDb.OleDbCommand
    
            Dim SQLEmail As String
    
            Con.ConnectionString = "PROVIDER = Microsoft.Jet.OLEDB.4.0; Data Source = |DataDirectory|Database1.mdb"
    
            Con.Open()
    
            SQLEmail = "UPdate [DateTimeTest] set [Datetime] = @DatePaid where [ID]=1  "
    
            cmd = New OleDb.OleDbCommand(SQLEmail, Con)
    
            cmd.Parameters.Add("@DatePaid", OleDb.OleDbType.DBDate).Value = Date.Now
    
            cmd.ExecuteNonQuery()
    
            Con.Close()

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 13, 2016 4:31 AM
  • User224181609 posted

    Brando, you are going above and beyond here! Now I see why I have been having do much trouble -- something that should be working is not working.

    I am showing the exact code I have been trying below.  I have commented-out the update query that works as it should, where DatePaid is explicitly provided.  Below that is an update query where the only change is that DatePaid is added as a parameter, which I believe is exactly as you have shown.  That Update query does not work.

     ' this one works
            'SQLEmail = "UPdate [Payments] set [AmountPaid] = @AmountPaid,  [DatePaid]= '7/5/2016', [AmountChips]=@AmountChips, [AmountRegistration]=@AmountRegistration where [PaymentID]=@PaymentID "
    
            ' this one does not work
            SQLEmail = "UPdate [Payments] set [AmountPaid] = @AmountPaid,  [DatePaid]= @DatePaid, [AmountChips]=@AmountChips, [AmountRegistration]=@AmountRegistration where [PaymentID]=@PaymentID "
    
            cmd.Parameters.Add("@DatePaid", OleDb.OleDbType.DBDate).Value = Date.Now
    
            cmd = New OleDb.OleDbCommand(SQLEmail, Con)
    
            cmd.Parameters.Add("@AmountPaid", OleDb.OleDbType.Numeric, 8).Value = LblAmount.Text
    
            cmd.Parameters.Add("@AmountChips", OleDb.OleDbType.Numeric, 8).Value = LblAmountChips.Text
    
            cmd.Parameters.Add("@AmountRegistration", OleDb.OleDbType.Numeric, 8).Value = LblAmountRegistration.Text
    
            cmd.Parameters.Add("@PaymentID", OleDb.OleDbType.Numeric, 8).Value = GVPayments.SelectedRow.Cells(1).Text

    I am beginning to wonder if possibly there is some difference in the date datatype of the Access database  for whatever version you are using and mine - I am using Access 2000 (yes, I know that's a tad old).  Unless you see something wrong here, I'm not sure where else to look!

    Tuesday, September 13, 2016 2:14 PM
  • User224181609 posted

    Brando I have marked your post as answer.

    Now -- your last post has not worked for me, but as you said, you have tested it. And I believe it is the correct answer so I marked it as the answer. There has to be something weird going on causing it to not work for me.

    Thank you for all your help.

    Thursday, September 15, 2016 2:11 AM
  • User283571144 posted

    Hi ClarkNK,

    Below that is an update query where the only change is that DatePaid is added as a parameter, which I believe is exactly as you have shown.  That Update query does not work.

    I suggest you could define a new datatable for test the Date/Time type.

    In this datatable only have one column.

    Then you could use the codes as my posted to test it.

    cmd.Parameters.Add("@AmountPaid", OleDb.OleDbType.Numeric, 8).Value = LblAmount.Text

    Besides, I think this codes will causes "Data type mismatch in criteria expression."

    Since the LblAmount.Text type is string and the Parameters type is Numeric, it will throw this expression.

    More details about my test demo, you could refer to follow codes:

        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Dim Con As New OleDb.OleDbConnection
            Dim cmd As New OleDb.OleDbCommand
            Dim SQLEmail As String
            Con.ConnectionString = "PROVIDER = Microsoft.Jet.OLEDB.4.0; Data Source = |DataDirectory|Database2.mdb"
            Con.Open()
            SQLEmail = "UPdate [DateTimeTest] set [Datetime] = '@DatePaid',[AmountPaid] = '@AmountPaid'  where [ID]=1  "
            cmd = New OleDb.OleDbCommand(SQLEmail, Con)
            cmd.Parameters.Add("@DatePaid", OleDb.OleDbType.DBDate).Value = Date.Now
    'Add this codes throw Data type mismatch in criteria expression cmd.Parameters.Add("@AmountPaid", OleDb.OleDbType.Numeric, 8).Value = "9" cmd.ExecuteNonQuery() Con.Close() End Sub

    Best Regards

    Brando

     

    Monday, September 19, 2016 10:16 AM
  • User224181609 posted

    Hello Brando, thank you for all the effort you have put in on this.

    I notice that I made an error in my last post, where I showed identical queries saying one worked an one did not. The one that worked was supposed to show an integer explicitly entered for the PaymentID

    Anyway, yes, I had already recognized the problem with the label text being a string and had tried converting to an integer, which did not work either. I tried every possible way I could think of.

    So, I finally abandoned the approach that was requiring what I was trying to do, and changed my database from a two table relational database to a single table, which allowed me to use a simple formview to do the updating. A relational database was not really the right approach for what I was doing anyway

     I have everything working now.

    Monday, September 19, 2016 12:20 PM
  • User-1199946673 posted

    Date.Now in VB.NET returns a DateTime including miliseconds. However, The DateTime datatype in an Access Database (or better said, a Jet Database Engine). is a OleAutomation Date, which doesn't include miliseconds

    Date.Now.ToOADate will do the job

    http://www.mikesdotnetting.com/article/92/ms-access-date-and-time-with-asp-net

    Thursday, September 29, 2016 8:33 PM