11 Replies - 6737 Views - Last Post: 15 October 2010 - 12:42 PM Rate Topic: -----

#1 eTech1  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 135
  • Joined: 27-August 10

Comparing Querystring to Text Type Mismatch

Posted 06 October 2010 - 07:19 AM

I'm using ASP.NET VB and an Access database. I had a field (cID) set in my table as a number, now that number has changed to a base 16 number for security reasons. I had to change the type in my Access database to text. Now I'm comparing a querystring to text where I use to compare a querystring to a number and now I'm getting a type mismatch.... seems a little backwards to me.

cCon.Open()
            Dim cSql As String = "SELECT TOP 1 * FROM conf WHERE cID = @cID;"
            Dim cCmd As New OleDbCommand(cSql, cCon)
            cCmd.Parameters.AddWithValue("@cID", Request.QueryString("cID"))
            Dim cReader As OleDbDataReader
            cReader = cCmd.ExecuteReader()


The error comes on line cReader = cCmd.ExecuteReader():
- System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

Anyone see my mistake?

Is This A Good Question/Topic? 0
  • +

Replies To: Comparing Querystring to Text Type Mismatch

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: Comparing Querystring to Text Type Mismatch

Posted 06 October 2010 - 12:49 PM

What is the value from the querystring? If the database field is setup as text and the querystring data is something like "1234556", then the AddWithValue could be sending it as an integer. Use the Add method instead and explicitly give the database type.
Was This Post Helpful? 1
  • +
  • -

#3 eTech1  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 135
  • Joined: 27-August 10

Re: Comparing Querystring to Text Type Mismatch

Posted 06 October 2010 - 12:58 PM

The value can include a, b, c, d, e and f. The one I tested did include some letters.
Was This Post Helpful? 0
  • +
  • -

#4 Frinavale  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 203
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: Comparing Querystring to Text Type Mismatch

Posted 13 October 2010 - 07:15 AM

I'm probably wrong here (feel free to correct me) because I am by no means a Database expert (I haven't used one in many years)....

But, I looked up the OleDBCommand.Parameters property and discovered that you should probably be using ?'s to indicate parameters in your command. (See the link for an example).

Hope this helps you...

-Frinny

This post has been edited by Frinavale: 13 October 2010 - 07:17 AM

Was This Post Helpful? 1
  • +
  • -

#5 eTech1  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 135
  • Joined: 27-August 10

Re: Comparing Querystring to Text Type Mismatch

Posted 13 October 2010 - 02:44 PM

The ?'s are an equally good alternative but I prefer to know exactly what I'm working with so I choose to go with the actual names. I am pretty new to Access DBs also but this is my understanding so far.

If I remember my solution right I believe I realized this issue was only coming up on pages where I was pulling the data from a querystring as an integer and it wasn't a database issue really.
Was This Post Helpful? 0
  • +
  • -

#6 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: Comparing Querystring to Text Type Mismatch

Posted 14 October 2010 - 04:34 AM

View PosteTech1, on 13 October 2010 - 05:44 PM, said:

The ?'s are an equally good alternative but I prefer to know exactly what I'm working with so I choose to go with the actual names. I am pretty new to Access DBs also but this is my understanding so far.

If I remember my solution right I believe I realized this issue was only coming up on pages where I was pulling the data from a querystring as an integer and it wasn't a database issue really.


Have you tried it with the ??
Was This Post Helpful? 0
  • +
  • -

#7 eTech1  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 135
  • Joined: 27-August 10

Re: Comparing Querystring to Text Type Mismatch

Posted 14 October 2010 - 06:21 AM

Are you asking because you think it may be the solution to my problem? This problem has been solved for a while now.

I've never tried it with the ?'s but from what I've read it all works the same.

Thanks.

This post has been edited by eTech1: 14 October 2010 - 08:16 AM

Was This Post Helpful? 0
  • +
  • -

#8 Frinavale  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 203
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: Comparing Querystring to Text Type Mismatch

Posted 14 October 2010 - 06:41 AM

I'm pretty sure that you have to use ? to signify parameters when using the OleDBCommand... try it and let me know how it goes.

-Frinny

This post has been edited by Frinavale: 14 October 2010 - 06:43 AM

Was This Post Helpful? 0
  • +
  • -

#9 eTech1  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 135
  • Joined: 27-August 10

Re: Comparing Querystring to Text Type Mismatch

Posted 14 October 2010 - 08:16 AM

But my code is working and I haven't used ?'s so I would say they both work just fine.
Was This Post Helpful? 0
  • +
  • -

#10 Frinavale  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 203
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: Comparing Querystring to Text Type Mismatch

Posted 15 October 2010 - 05:56 AM

:) Thanks for that info.
Like I said I haven't had to use a database in years...and I've never worked with Access in a .NET application.

-Frinny
Was This Post Helpful? 0
  • +
  • -

#11 eTech1  Icon User is offline

  • D.I.C Head

Reputation: 6
  • View blog
  • Posts: 135
  • Joined: 27-August 10

Re: Comparing Querystring to Text Type Mismatch

Posted 15 October 2010 - 06:33 AM

View PostFrinavale, on 15 October 2010 - 04:56 AM, said:

Like I said I haven't had to use a database in years...and I've never worked with Access in a .NET application.

-Frinny


I wouldn't recommend you start working with Access either lol. It's unimpressive at best.
Was This Post Helpful? 0
  • +
  • -

#12 Frinavale  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 203
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: Comparing Querystring to Text Type Mismatch

Posted 15 October 2010 - 12:42 PM

Are you still having problems with your code?

If so, it's probably because your cID is supposed to be an Integer (or something) but your QueryString value for cID is empty.

You should either check if the Request.QueryString("cID") is nothing before attempting to use it.

Like this:

If String.IsNullOrEmpty(Request.QueryString("cID")) = False Then
  cCon.Open()
  Dim cSql As String = "SELECT TOP 1 * FROM conf WHERE cID = @cID;"
  Dim cCmd As New OleDbCommand(cSql, cCon)
  cCmd.Parameters.AddWithValue("@cID", Request.QueryString("cID"))
  Dim cReader As OleDbDataReader
  cReader = cCmd.ExecuteReader()
Else
  'This is the case when cID is empty.....
  'Either inform the user that there is a problem or
  'fix the problem by setting cID to some "default" number
End If


You could go one step further and validate that the cID in the QueryString is a number:
Dim cID as Integer = 0
If String.IsNullOrEmpty(Request.QueryString("cID")) = False AndAlso _
   Integer.TryParse(Request.QueryString("cID"), cID) = True Then
  cCon.Open()
  Dim cSql As String = "SELECT TOP 1 * FROM conf WHERE cID = @cID;"
  Dim cCmd As New OleDbCommand(cSql, cCon)
  cCmd.Parameters.AddWithValue("@cID", cID)
  Dim cReader As OleDbDataReader
  cReader = cCmd.ExecuteReader()
Else
  'This is the case when cID is empty or it is not a number
  'Either inform the user that there is a problem or
  'fix the problem by setting cID to some "default" number
End If




-Frinny

This post has been edited by Frinavale: 15 October 2010 - 12:45 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1