none
filling a datagridview from a stored proceduer RRS feed

  • Question

  • I am trying to fill a datagridview from a SP but it is not working

     

     

    Private Sub buildXLS(ByVal vennum As String)

    Dim objconn As New SqlConnection(connstring)

    Dim ocomm As New SqlCommand

    ocomm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@vendornum ", System.Data.SqlDbType.Text, 15, "vennum"))

    ocomm.Parameters("@vendornum ").Value = vennum

    ocomm.Connection = objconn

    ocomm.CommandType = CommandType.StoredProcedure

    ocomm.CommandText = "sp_forcastBeverly"

    objconn.Open()

    ocomm.ExecuteNonQuery()

    Dim da As SqlDataAdapter = New SqlDataAdapter

    da.SelectCommand = ocomm

    Dim ds As DataSet = New DataSet

    da.Fill(ds)

    DataGridView1.DataSource = ds

    objconn.Close()

    end sub

     

     

    I also tried

    Dim sqlconnection As New SqlConnection(connstring)

    Dim sqlcommand As New SqlCommand("sp_forcastBeverly", sqlconnection)

    sqlcommand.CommandType = CommandType.StoredProcedure

    sqlcommand.Parameters.AddWithValue("@vendornum", Trim(vennum))

    sqlconnection.Open()

    DataGridView1.DataSource = sqlcommand.ExecuteReader

    sqlconnection.Close()

    sqlcommand.Dispose()

    Thursday, May 17, 2007 3:16 PM

Answers

  • Dim sqlconnection As New SqlConnection(connstring)

    Dim sqlcommand As New SqlCommand("sp_forcastBeverly", sqlconnection)

    sqlcommand.CommandType = CommandType.StoredProcedure

    sqlcommand.Parameters.AddWithValue("@vendornum", Trim(vennum))


      Dim dtForcast As DataTable = new DataTable
      dtForcast.Locale = CultureInfo.CurrentCulture
      Dim DA as SqlDataAdapter = new SqlDataAdapter(sqlcommand)
      DA.Fill(dtForcast)
     
      DataGridView1.DataSource = dtForcast
    Thursday, May 17, 2007 8:29 PM

All replies

  • It is helpful to indicate what isn't working, or what the error message is when you post a question.

     

    From this snippet, though, I can see that you need to remove the line:

     

    ocomm.ExecuteNonQuery()

     

    The data adapter will execute the query when you call Fill, so you don't need to explicitly execute it yourself.

     

    Add a Try / Catch in your VB procedure. In your Catch block you can use Debug.Writeline or MessageBox.Show to see what the exception is.

     

    Some common types of exceptions:

    1) the stored procedure might have an error in it

    2) the connection string might not be correct

    3) the user account in the connection string, or the logged on user if it is using windows authentication, may not have permissions to execute the stored procedure

    4) the stored procedure might not be returning records

    Thursday, May 17, 2007 3:34 PM
  • oops

    the data grid view is not filling, there should be 198 rows,

    I tested the sp

     

    sp_forcastBeverly @vendornum = ar003800 and i has 198 rows

    Thursday, May 17, 2007 3:44 PM
  • I have tried this way also

     

    Dim ds As DataSet

    Dim myconnection As SqlConnection

    Dim mycommand As SqlDataAdapter

    Dim parven As New SqlParameter

    myconnection = New SqlConnection(connstring)

    mycommand = New SqlDataAdapter("sp_forcastBeverly", myconnection)

    With mycommand.SelectCommand

    .CommandType = CommandType.StoredProcedure

    .Parameters.Add("@vendornum", SqlDbType.VarChar, 10).Value = Trim(vennum)

    End With

    ds = New DataSet()

    mycommand.Fill(ds, "mydata")

    DataGridView1.DataSource = ds

    Thursday, May 17, 2007 3:49 PM
  • Set the DataGridView1.DataMember = "mydata".

     

    Thursday, May 17, 2007 6:39 PM
  • Dim sqlconnection As New SqlConnection(connstring)

    Dim sqlcommand As New SqlCommand("sp_forcastBeverly", sqlconnection)

    sqlcommand.CommandType = CommandType.StoredProcedure

    sqlcommand.Parameters.AddWithValue("@vendornum", Trim(vennum))


      Dim dtForcast As DataTable = new DataTable
      dtForcast.Locale = CultureInfo.CurrentCulture
      Dim DA as SqlDataAdapter = new SqlDataAdapter(sqlcommand)
      DA.Fill(dtForcast)
     
      DataGridView1.DataSource = dtForcast
    Thursday, May 17, 2007 8:29 PM
  • Nope still did not fill
    Friday, May 18, 2007 1:35 PM