Running a query

Dwizz · May 3rd, 2005, 09:02 AM

I tried to query single DDL, and it works, but when I try multiple DDL's it doesn't seem to like it,
any suggestions anyone?

gbianchi · May 3rd, 2005, 09:09 AM

hi there..

could you be more explicit?? you were filling only one.. are you trying to get results for more than one on the same query???

HTH

Gonzalo

Dwizz · May 3rd, 2005, 09:50 AM

I was only using one DDL to test my query, and it worked, when I tried to use two or three DDL's to test my query nothing when I know it should have chucked out some results, I'm sure my query works, its probably the way I've written the syntax for the onButton click event (see previous posts).

BrianWren · May 3rd, 2005, 01:35 PM

What is the intention of MAINquery()? Does it return a string or what?

If you put a messagebox in MAINquery() to report the values that arive in board, chassis and hDDType, what do you find?

Prashant.k.m · May 4th, 2005, 12:27 AM

hi dear,

Quote:

quote:

I tried to query single DDL, and it works, but when I try multiple DDL's it doesn't seem to like it,
any suggestions anyone?

I feel No one here is clear about what you are saying.

What we all know is
1) you have 3 DDL
2) you have a button
3) In the button_onClick you call the MAINquery(ddl1.selectedtext,ddl2.selectedtext,ddl3 .selectedtext)

Now just answer

1) Do you receive those params correctly in the MAINquery funtion (give response.write of all 3 and check)?
2) If Yes, Do you build the sql string correctly from these params( give response.write(strSQL) , copy it to QUERY analyser and test whether it returns records correctly ) ?
3) If Yes, Do you see the same result in your page in the databound control(Is it a grid/list...)?
4) Are you getting any errors?

Be cool
Prashant

3)

Dwizz · May 4th, 2005, 03:37 AM

Hey all,
First off let me apologise for not being clear, now I have a MS Access Database which has information on different servers, hence the DDL are titled Chassis, Board and HDDType. Now I'm using VB.NET on the backend to query the MS DB, I know for 100% that the query works because I tried it in MS Access (not the same code as it had to be modified to work with Access), yet when I try to use the query in VB.NET it doesn't return any values when I know values should be returned (I'm databinding the results to another DDL called DDL4), below is my code. All help is greatly appreciated.

Code:

Function MAINquery(ByVal board As String, ByVal chassis As String, ByVal hDDType As String) As System.Data.DataSet
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Documents an"& _
"d Settings\Desktop\FinSample.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

        Dim queryString As String = "SELECT [System].* FROM [System] WHERE (([System].[Board] = @Board) AND ([System]."& _
"[Chassis] = @Chassis) AND ([System].[HDDType] = @HDDType))"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dbParam_board As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_board.ParameterName = "@Board"
        dbParam_board.Value = board
        dbParam_board.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_board)
        Dim dbParam_chassis As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_chassis.ParameterName = "@Chassis"
        dbParam_chassis.Value = chassis
        dbParam_chassis.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_chassis)
        Dim dbParam_hDDType As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
        dbParam_hDDType.ParameterName = "@HDDType"
        dbParam_hDDType.Value = hDDType
        dbParam_hDDType.DbType = System.Data.DbType.String
        dbCommand.Parameters.Add(dbParam_hDDType)

        Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
        dataAdapter.SelectCommand = dbCommand
        Dim dataSet As System.Data.DataSet = New System.Data.DataSet
        dataAdapter.Fill(dataSet)

        Return dataSet
    End Function

Sub Button1_Click(sender As Object, e As EventArgs)
    DropDownList4.DataTextField = "Server"
    DropDownList4.Datasource = MAINquery(Chassis1.SelectedValue, Board1.SelectedValue, HDDType1.SelectedValue)
    DropDownList4.DataBind()
End Sub

When I used only one param such as Chassis (it returned two values that I expected), when I tried Board (it returned only the one value I expected) and when I tried HDDType (again it returned One Value as expected), its only when I merge them together or even use 2 params it doesn't like it.

Prashant.k.m · May 4th, 2005, 05:41 AM

I dont find any problem in this, Could you try without using the command params, so that we can run the same query in access after the values are inserted in the query.

May be something like given below

    Function MAINquery(ByVal board As String, ByVal chassis As String, ByVal hDDType As String) As System.Data.DataSet
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Documents an" & _
"d Settings\Desktop\FinSample.mdb"
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString )

        Dim queryString As String = "SELECT [System].* FROM [System] WHERE (([System].[Board] = '" & board & "') AND ([System]." & _
"[Chassis] = '" & chassis & "') AND ([System].[HDDType] = '" & hDDType & "'))"

        Response.Write("Query:" & queryString)
        '// copy this query to access and run if it works

        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
        dataAdapter.SelectCommand = dbCommand
        Dim dataSet As System.Data.DataSet = New System.Data.DataSet
        dataAdapter.Fill(dataSet)

        Response.Write("Rows:" & dataSet.Tables(0).Rows.Count)
        '// see the no is correct

        Return dataSet
    End Function

Dwizz · May 4th, 2005, 05:51 AM

Okay Just tried the new code, and when I click the Submit button it returns the following

Query:SELECT [System].* FROM [System] WHERE (([System].[Board] = '1U Rackmount') AND ([System].[Chassis] = 'Xeon 800FSB') AND ([System].[HDDType] = 'SCSI'))Rows:0

so now what???
When I tried the code in MS Access it returned 1 Value.

D

planoie · May 4th, 2005, 06:21 AM

Dwizz,

In the OleDbDataAdapter, you can't use the SqlClient syntax for parameters: "@variable". You have to use ? for each parameter and make sure that you add them to the parameters collection in the same order they appear in the command text. So the problem you are having is that the database is trying to actually test the value @HDDType and so forth. I guess it's not breaking the query, but it's not working regardless.

-Peter

Dwizz · May 4th, 2005, 07:00 AM

So how would I go about changing that part of the code, and what I don't understand is if I just use one param it returns the values expected, but when you use two or three it does nothing??

I'm confused