Question Problem retrieving information form a database

[ucp8]

Hi guys,

I have been palying around with an SQl statement this afternoon, which works outside of my VB.NET application in Management Studio but will not work in the application.

Here is the code I am using:

[SIZE="2"]testSql = "SELECT TestDetails.[TestID], TestDetails.[SubjectArea], TestDetails.[TestDate] FROM TestDetails WHERE TestDetails.[TestDate] = '" + todaysDate + "' AND TestDetails.[TestID] NOT IN (SELECT TestTakenDetails.[TestID] FROM TestTakenDetails WHERE TestTakenDetails.[PupilID] = " + pupilID.ToString + ")"

testDa = New OleDb.OleDbDataAdapter(testSql, getTestConnect)

' Fill dataset with test information
testDa.Fill(testDs, "currentTest")
' Initialise maxQuestions integer
maxTests = testDs.Tables("currentTest").Rows.Count[/SIZE]

When I run this code I get the Data type mismatch in criteria expression Exception. I know this is because of the ' quotes I am using around the todaysDate variable. So I have taken them out and am using the following instead:

[SIZE="2"]WHERE TestDetails.[TestDate] = " + todaysDate.ToString + " AND TestDetails.[TestID][/SIZE]

This, although a valid SQL statement, like the previous one, returns no records to be used in my OleDb.OleDbDataAdapter, testDa.

Can anyone see why it is not returning the record that it should? When I run the SQL statement in Management Studio I get the record back, but not with the statement in my code.


Also, I was using a simpler statement earlier:

[SIZE="2"]testSql = "SELECT TestDetails.[TestID], TestDetails.[SubjectArea], TestDetails.[TestDate] FROM TestDetails WHERE TestDetails.[TestDate] = '" + todaysDate + "'"[/SIZE]

This statement worked perfectly, returning the tests that had todays date. But when I tried using

WHERE TestDetails.[TestDate] = '" + todaysDate + "'"

in my second statement, I got the Data type mismatch in criteria expression Exception. This makes no sense. Why would it let me use it in the first statement, but not the second?

Also, testSql is a String, as you have to pass the sql statement as a string to the data adapter. There may be problems arising from this.


Can anyone tell me how I can fix this? Thanks for your help

 

[jmcilhinney]

If you do the right thing and use parameters when inserting variables into SQL statements then problems like this just disappear. NEVER use string concatenation to insert variables into SQL statements if it can possibly be avoided.

testDa = New OleDbDataAdapter("SELECT TestID, SubjectArea, TestDate FROM TestDetails WHERE TestDate = @TestDate AND TestID NOT IN (SELECT TestID FROM TestTakenDetails WHERE PupilID = @PupilID", _
                              getTestConnect)

With testDa.SelectCommand.Parameters
    .AddWithValue("@TestDate", Date.Today)
    .AddWithValue("@PupilID", pupilID)
End With

 

[ucp8]

Thanks very much for the help jmcilhinney. I tried your method but got the same result. It doesn't return any records from the database. I cannot see why it works fine in Management Studio, but will not work in this application. Would there be anything else that I am not doing?

Thanks again for your help. It's most appreciated

 

[Tom]

Why are you using SQL Server Management Studio but then using OLEDB instead of sqlclient in your coding? Is this an Access or SQL database?

Also in your query is PupilId a string or number? Although using parameters as JM posted, shoudve corrected this.

WHERE TestTakenDetails.[PupilID] = " + pupilID.ToString

What datatype is TestDetails.[TestDate]? If DateTime are you accounting for the time included in the field?

 

[ucp8]

I was originally using OLEDB in my project, but then a colleague tested the statement for me in SQL Server Management Studio. My database is an Access database.

PupilID is an Integer, but it has to be converted to a String to be used with the OLEDB Data Adapter. The date variable is also a string because of this. In the Access database, the TestDetails.[TestDate] element is a Text data type.

I am aware that I technically should be using a Date/Time type, but because I have to use a String to check for dates, I have used a Text type.

Does this information help with anything that might be casuing the problem?

Thanks for your help Tom

 

[Tom]

Ok since the date is actually text, how exactly is it displayed and does it include the time?

Since it is a string (unless you convert datatypes back and forth) now you must make sure the parameter text & formatting match exactly to your text field. ex. '3/12/2009' does not equal '3-12-2009' as text.

Why do you have to use a string to check for dates?

Also you do not need to convert numbers into text for a select statement and please take a look in the help file at OleDbDataAdapter Class, it will show how to use parameters in your queries, including identifing the parameters datatype.

 

[jmcilhinney]

You should not be using strings for anything but strings. If data represents dates then it should be dates. If data represents numbers it should be numbers. Do you really think that everyone who uses Access databases uses Text for all their columns because they have to use strings to query? They do NOT have to use strings to query, as I have shown. You just had to convert everything to strings because you were using string concatenation to build your SQL. That is bad, Bad, BAD! As I've shown, you can use any data type you like with parameters, so you should change your database to use the correct types for the columns.

 

[Tom]

Agree with JM's post.

Also to make everything more confusing (or at least still confuses me slightly) is the use of parameters.AddWithValue. JM perhaps you can offer some clarification about it but the way I am understanding it; should be used only for string parameters.

From the help file:

AddWithValue replaces the SqlParameterCollection.Add method that takes a String and an Object.

The overload of Add that takes a string and an object was deprecated because of possible ambiguity with the SqlParameterCollection.Add overload that takes a String and a SqlDbType enumeration value where passing an integer with the string could be interpreted as being either the parameter value or the corresponding SqlDbType value.

Use AddWithValue whenever you want to add a parameter by specifying its name and value.

The following example demonstrates how to use the AddWithValue method.

(My comments : Notice in the MSDN example they only use AddWithValue when its a string and use .Add when its a different datatype.)

        Dim command As New SqlCommand(commandText, connection)

        ' Add CustomerID parameter for WHERE clause.
        command.Parameters.[COLOR="Red"]Add[/COLOR]("@ID", SqlDbType.[COLOR="red"]Int[/COLOR])
        command.Parameters("@ID").Value = customerID

        ' Use AddWithValue to assign Demographics.
        ' SQL Server will implicitly convert strings into XML.
        command.Parameters.[COLOR="red"]AddWithValue[/COLOR]("@demographics", [COLOR="red"]demoXml[/COLOR])

Now I havent had any problems passing different datatypes in it but I'm wondering if this is correct or not. Should we use .Add when not a string perhaps or at least assign a differing datatype to the parrameter such as

cmd.Parameters.AddWithValue("@KeyId", intId)
cmd.Parameters("@KeyId").DbType = DbType.Int32

 

[JohnH]

should be used only for string parameters.

No, it means Add(parameterName as String, value as Object) where object can be anything and the Add method will autodetect the data type.

My comments : Notice in the MSDN example they only use AddWithValue when its a string and use .Add when its a different datatype.

They just provide one example of each, the data types of the examples are arbitrary.

If you're going to loop some values you will add the parameter with the Add method, then in loop you reuse the existing parameter and just set the value, like this:

create command
add parameter
loop values
   set parameter value
   execute command
end loop

 

[jmcilhinney]

Further to what JohnH said, basically the only difference between Add and AddWithValue is that AddWithValue creates a parameter and sets its value while Add just creates the parameter. If you're only going to execute the command once with a single parameter value then you should use AddWithValue for simplicity. You could use Add but it's more code so why bother? If you're going to execute a command multiple times with different parameter values then you should use Add. That way you can set the parameter's Value property explicitly each time.

One of the most common uses for the Add method is when you create a data adapter and add parameters to the DeleteCommand, InsertCommand and UpdateCommand. In that case you don't provide parameter values explicitly at all. You simply tell the command what column of the source DataTable to get the parameter values from by passing the column name as an argument when calling Add. You then call Update on your DataAdapter and the parameter values are set implicitly for each row.

 

[Tom]

Thanks John & JM