SqlException error

akibaMaila · August 23rd, 2005, 10:54 AM

Below is my code in which I am getting an error an the error occurs at the sqldataadapter.SelectCommand.ExecuteNonQuery() step

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        sqldataadapter.SelectCommand = New SqlCommand
        sqldataadapter.SelectCommand.Connection = connstr

        sqldataadapter.SelectCommand.CommandText = "select * from RES_Forcasting2 where mrn = " & TextBox1.Text

        sqldataadapter.SelectCommand.CommandType = CommandType.Text
        ' open database connection
        connstr.Open()

        'now execute the command
        sqldataadapter.SelectCommand.ExecuteNonQuery()

        'fill the dataset object with data
        sqldataadapter.Fill(mydataset, "res_forcasting2")

        'close database connection
        connstr.Close()

        'set the datagrid properties to bind it to our data
        DataGrid1.DataSource = mydataset
        DataGrid1.DataMember = "res_forcasting2"
    End Sub

My program runs find until I added the "where" clause in the select statement. Any help would be greatly appreciated. Thanks

cliffd64 · August 23rd, 2005, 12:13 PM

Quote:

quote:Originally posted by akibaMaila
Below is my code in which I am getting an error an the error occurs at the sqldataadapter.SelectCommand.ExecuteNonQuery() step

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        sqldataadapter.SelectCommand = New SqlCommand
        sqldataadapter.SelectCommand.Connection = connstr

        sqldataadapter.SelectCommand.CommandText = "select * from RES_Forcasting2 where mrn = " & TextBox1.Text

        sqldataadapter.SelectCommand.CommandType = CommandType.Text
        ' open database connection
        connstr.Open()

        'now execute the command
        sqldataadapter.SelectCommand.ExecuteNonQuery()

        'fill the dataset object with data
        sqldataadapter.Fill(mydataset, "res_forcasting2")

        'close database connection
        connstr.Close()

        'set the datagrid properties to bind it to our data
        DataGrid1.DataSource = mydataset
        DataGrid1.DataMember = "res_forcasting2"
    End Sub

My program runs find until I added the "where" clause in the select statement. Any help would be greatly appreciated. Thanks

I have never needed to use the code :

sqldataadapter.SelectCommand.ExecuteNonQuery()

Usually I simply set up the query string as you did, and then open the connection and fill the dataset. Try commenting out that command. Also, when I am having issues with the SQL, I use MessageBox.Show on the command text to make sure the SQL submitted is what I expected it to be :)

akibaMaila · August 23rd, 2005, 01:44 PM

I comment out the sqldataadapter.SelectCommand.ExecuteNonQuery()
code and code the MessageBox.Show as suggested but it still does not work. The MessageBox.Show does show that my sql is as it should be.

I should mention that my form is populated with data on a form level. In addition, it has a textbox1 and a button1 so that I can filter the data and only show the data i am interested in.

Any suggestions would be greatly appreciated.

cliffd64 · August 25th, 2005, 09:14 AM

Quote:

quote:Originally posted by akibaMaila
I comment out the sqldataadapter.SelectCommand.ExecuteNonQuer y()
code and code the MessageBox.Show as suggested but it still does not work. The MessageBox.Show does show that my sql is as it should be.

I should mention that my form is populated with data on a form level. In addition, it has a textbox1 and a button1 so that I can filter the data and only show the data i am interested in.

Any suggestions would be greatly appreciated.

Here's some example code that I use to do essentially the same type of thing. It sounds to me like your connection string might be the problem - it doesn't seem like the connection is being established:

here is some working code that I use...

Code:

' set up the datasets etc
        Dim objConnection As SqlConnection = New _
            SqlConnection("server=wfntmetersidb1; database=NUEVEE_MAIN; user id=userid; password=password")
        Dim objDataAdapter As New SqlDataAdapter
        '        Dim objDataSet As DataSet = New DataSet

        ' Set the SelectCommand Properties.....
        objDataAdapter.SelectCommand = New SqlCommand
        objDataAdapter.SelectCommand.Connection = objConnection
        objDataAdapter.SelectCommand.CommandText = _
        "SELECT dbo.GroupHdr.name AS Groupnme, " & _
        "dbo.Recorder.recid as Recorder, " & _
        "dbo.Account.name AS Name " & _
        "FROM dbo.GroupHdr INNER JOIN " & _
        "dbo.GroupElement ON dbo.GroupHdr.gid = dbo.GroupElement.gid " & _
        "INNER JOIN dbo.Recorder ON dbo.GroupElement.elementid = dbo.Recorder.rid " & _
        "INNER JOIN dbo.Account ON dbo.Recorder.aid = dbo.Account.aid " & _
        "WHERE dbo.GroupHdr.Name = '" & cboGroup.SelectedValue & "' " & _
        "ORDER BY dbo.GroupHdr.name, dbo.Recorder.recid"

        objDataAdapter.SelectCommand.CommandType = CommandType.Text

        ' Open the database Connection
        objConnection.Open()

        objDataSet = Nothing
        objDataSet = New DataSet

        ' fill the dataset with data
        objDataAdapter.Fill(objDataSet, "groups")

        ' MessageBox.Show(objDataSet.Tables(0).Rows.Count())

        objConnection.Close()

The line of code below can be uncommented to show you the number of rows returned:

Code:

' MessageBox.Show(objDataSet.Tables(0).Rows.Count())