Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
VB.NET 2002/2003 Basics For coders who are new to Visual Basic, working in .NET versions 2002 or 2003 (1.0 and 1.1).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB.NET 2002/2003 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 23rd, 2005, 10:54 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default SqlException error

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
 
Old August 23rd, 2005, 12:13 PM
Authorized User
 
Join Date: May 2005
Location: Berlin, CT, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 :)


 
Old August 23rd, 2005, 01:44 PM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old August 25th, 2005, 09:14 AM
Authorized User
 
Join Date: May 2005
Location: Berlin, CT, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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())







Similar Threads
Thread Thread Starter Forum Replies Last Post
Sqlexception monika.vasvani ASP.NET 2.0 Basics 0 June 12th, 2008 03:28 AM
InitializeCulture causing Sqlexception Error... greglh BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 2 March 14th, 2008 11:16 AM
Catching a SQLException error digriz60 ASP.NET 1.0 and 1.1 Basics 5 January 14th, 2008 03:14 PM
java.sql.SQLException muthumari_p2003@yahoo.com SQL Server 2000 1 March 22nd, 2006 08:57 AM
java.sql.SQLException: Error loading JDBC Driver jdphjp Java Databases 1 August 5th, 2004 12:46 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.