none
error code:-2146232060 RRS feed

  • Question

  • Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim constring As String = "Data Source=(local)\SQLEXPRESS;Initial Catalog=try1;Integrated Security=True" Dim strSQL As String strSQL = "declare @cust_id nchar(5)," & _ "@order_date datetime" & vbCrLf & _ "set @cust_id = 'rose' " & vbCrLf & _ "set @order_date='2/1/1989'" & vbCrLf & _ "select orderid,orderdate,shippeddate," & _ "datediff(day, @order_date,shippeddate) " & _ "as days_to_ship from order where " & _ "customerid = @cust_id and orderdate = @order_date" TextBox1.Text = strSQL Dim da As New SqlDataAdapter(strSQL, constring) Dim tb As New DataTable da.Fill(tb) DataGridView1.DataSource = tb End Sub
    • Moved by Helen Zhou Tuesday, April 19, 2011 6:09 AM (From:Windows Forms Data Controls and Databinding)
    Saturday, April 16, 2011 6:15 PM

Answers

  • If you step through your code, what line throws the exception and what is the detail? One thing to suggest, replace "(local)" in you connection string with a dot ".".

    Also, it is hard to imagine why you go through the trouble of declaring variables in your sql string and then setting them. If you need to pull values out of controls (textboxes, etc) then you should create a SqlCommand and add Parameters to it. This prevevnts sql injection attacks as well.


    Bob - www.crowcoder.com
    Monday, April 18, 2011 1:15 PM
  • Hello kajukoya,

    Welcome to the ADO.NET DataSet Forum!

    I do agree with Bob, could you please try his suggestion? Moreover, I think if you want to use ADO.NET to connect the database, you should use the SqlConnection and SqlCommand objects, and You won't connect to the Database until you open the SqlConnection Object. After completing all operations about DB, you should close the SqlConnection Object.

    Here is an example:

    Imports System Imports System.Data
    Imports System.Data.SqlClient
    Imports Microsoft.VisualBasic Public Class Sample

      Public Shared Sub Main()
        Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;" & _
                                                           "Integrated Security=SSPI;Initial Catalog=northwind")

        Dim catCMD As SqlCommand = nwindConn.CreateCommand()
        catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"

        nwindConn.Open()

        Dim myReader As SqlDataReader = catCMD.ExecuteReader()

        Do While myReader.Read()
          Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
        Loop

        myReader.Close()
        nwindConn.Close()
      End Sub
    End Class

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, April 20, 2011 3:04 AM
    Moderator

All replies

  • Hi Kajukoya,

    Would you please format the code properly to make it a little easier to see, and give more information abou what you are trying to do.

    Sincerely,


    Helen Zhou [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, April 18, 2011 8:41 AM
  • If you step through your code, what line throws the exception and what is the detail? One thing to suggest, replace "(local)" in you connection string with a dot ".".

    Also, it is hard to imagine why you go through the trouble of declaring variables in your sql string and then setting them. If you need to pull values out of controls (textboxes, etc) then you should create a SqlCommand and add Parameters to it. This prevevnts sql injection attacks as well.


    Bob - www.crowcoder.com
    Monday, April 18, 2011 1:15 PM
  • Hi Kajukoya,

    This forum is dedicated to Windows Forms technology. I'm moving this thread from Base "Windows Forms" to "ADO.NET DataSet" forum, since this issue is related to it. There are more ADO.NET DataSet experts in that forum.

    Sincerely,


    Helen Zhou [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, April 19, 2011 6:08 AM
  • Hello kajukoya,

    Welcome to the ADO.NET DataSet Forum!

    I do agree with Bob, could you please try his suggestion? Moreover, I think if you want to use ADO.NET to connect the database, you should use the SqlConnection and SqlCommand objects, and You won't connect to the Database until you open the SqlConnection Object. After completing all operations about DB, you should close the SqlConnection Object.

    Here is an example:

    Imports System Imports System.Data
    Imports System.Data.SqlClient
    Imports Microsoft.VisualBasic Public Class Sample

      Public Shared Sub Main()
        Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;" & _
                                                           "Integrated Security=SSPI;Initial Catalog=northwind")

        Dim catCMD As SqlCommand = nwindConn.CreateCommand()
        catCMD.CommandText = "SELECT CategoryID, CategoryName FROM Categories"

        nwindConn.Open()

        Dim myReader As SqlDataReader = catCMD.ExecuteReader()

        Do While myReader.Read()
          Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", myReader.GetInt32(0), myReader.GetString(1))
        Loop

        myReader.Close()
        nwindConn.Close()
      End Sub
    End Class

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, April 20, 2011 3:04 AM
    Moderator