Having Data Fill Problem

Hi dimante,

which error? can you show your code?

Cheers!

it's that simple just this fill command is in a button event.  The error returned is "System Error"  I can preview data no problem from the adapter...

SqlDataAdapter1.Fill(DataSet11, "your_tblName_here")

As emoreau mentions, you need to give us a bit more information, and perhaps your code.
Without that, we can only guess what going wrong.

Is the complete error message "System Error" or is there further information there?

I am guessing thhat you are using the Data Access components from the toolbox, given that you say you can 'preview' the data ok.
Is it that those controols have already called the .Fill method, which then causes the error when you call it again?

Please post your code, including the Designere-generated code pertaining to the data access.

Best Regards,

David

Remember you asked for it LOL:

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents DataSet11 As ORStudentCreate.DataSet1
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.Button1 = New System.Windows.Forms.Button
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.DataSet11 = New ORStudentCreate.DataSet1
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=ROCKET;packet size=4096;user id=sa;data source=""3dserv\SQL20053D"";" & _
        "persist security info=False;initial catalog=DI"
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "ADST", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("STATUS", "STATUS"), New System.Data.Common.DataColumnMapping("SCHOOLNUM", "SCHOOLNUM"), New System.Data.Common.DataColumnMapping("LASTNAME", "LASTNAME"), New System.Data.Common.DataColumnMapping("FIRSTNAME", "FIRSTNAME"), New System.Data.Common.DataColumnMapping("MIDDLENAME", "MIDDLENAME"), New System.Data.Common.DataColumnMapping("PERMNUM", "PERMNUM"), New System.Data.Common.DataColumnMapping("BIRTHDATE", "BIRTHDATE"), New System.Data.Common.DataColumnMapping("PRNTGUARD", "PRNTGUARD"), New System.Data.Common.DataColumnMapping("MAILADDR", "MAILADDR"), New System.Data.Common.DataColumnMapping("CITY", "CITY"), New System.Data.Common.DataColumnMapping("ZIPCODE", "ZIPCODE"), New System.Data.Common.DataColumnMapping("STATE", "STATE"), New System.Data.Common.DataColumnMapping("TELEPHONE", "TELEPHONE"), New System.Data.Common.DataColumnMapping("BIRTHPLACE", "BIRTHPLACE"), New System.Data.Common.DataColumnMapping("HOMELNG", "HOMELNG"), New System.Data.Common.DataColumnMapping("PRIMARYLNG", "PRIMARYLNG"), New System.Data.Common.DataColumnMapping("DSTLINK", "DSTLINK")})})
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(256, 400)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "Button1"
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT STATUS, SCHOOLNUM, LASTNAME, FIRSTNAME, MIDDLENAME, PERMNUM, BIRTHDATE, PR" & _
        "NTGUARD, MAILADDR, CITY, ZIPCODE, STATE, TELEPHONE, BIRTHPLACE, HOMELNG, PRIMARY" & _
        "LNG, DSTLINK FROM SASI.ADST WHERE (STATUS = '')"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO SASI.ADST(STATUS, SCHOOLNUM, LASTNAME, FIRSTNAME, MIDDLENAME, PERMNUM" & _
        ", BIRTHDATE, PRNTGUARD, MAILADDR, CITY, ZIPCODE, STATE, TELEPHONE, BIRTHPLACE, H" & _
        "OMELNG, PRIMARYLNG, DSTLINK) VALUES (@STATUS, @SCHOOLNUM, @LASTNAME, @FIRSTNAME," & _
        " @MIDDLENAME, @PERMNUM, @BIRTHDATE, @PRNTGUARD, @MAILADDR, @CITY, @ZIPCODE, @STA" & _
        "TE, @TELEPHONE, @BIRTHPLACE, @HOMELNG, @PRIMARYLNG, @DSTLINK); SELECT STATUS, SC" & _
        "HOOLNUM, LASTNAME, FIRSTNAME, MIDDLENAME, PERMNUM, BIRTHDATE, PRNTGUARD, MAILADD" & _
        "R, CITY, ZIPCODE, STATE, TELEPHONE, BIRTHPLACE, HOMELNG, PRIMARYLNG, DSTLINK FRO" & _
        "M SASI.ADST"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@STATUS", System.Data.SqlDbType.VarChar, 1, "STATUS"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SCHOOLNUM", System.Data.SqlDbType.VarChar, 3, "SCHOOLNUM"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LASTNAME", System.Data.SqlDbType.VarChar, 40, "LASTNAME"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FIRSTNAME", System.Data.SqlDbType.VarChar, 20, "FIRSTNAME"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MIDDLENAME", System.Data.SqlDbType.VarChar, 13, "MIDDLENAME"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PERMNUM", System.Data.SqlDbType.VarChar, 12, "PERMNUM"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BIRTHDATE", System.Data.SqlDbType.DateTime, 8, "BIRTHDATE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PRNTGUARD", System.Data.SqlDbType.VarChar, 35, "PRNTGUARD"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MAILADDR", System.Data.SqlDbType.VarChar, 35, "MAILADDR"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 24, "CITY"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ZIPCODE", System.Data.SqlDbType.VarChar, 10, "ZIPCODE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@STATE", System.Data.SqlDbType.VarChar, 2, "STATE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TELEPHONE", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(15, Byte), CType(0, Byte), "TELEPHONE", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BIRTHPLACE", System.Data.SqlDbType.VarChar, 20, "BIRTHPLACE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@HOMELNG", System.Data.SqlDbType.VarChar, 3, "HOMELNG"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PRIMARYLNG", System.Data.SqlDbType.VarChar, 3, "PRIMARYLNG"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DSTLINK", System.Data.SqlDbType.Decimal, 5, System.Data.ParameterDirection.Input, False, CType(7, Byte), CType(0, Byte), "DSTLINK", System.Data.DataRowVersion.Current, Nothing))
        '
        'DataSet11
        '
        Me.DataSet11.DataSetName = "DataSet1"
        Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(576, 459)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

   
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click





    End Sub


    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SqlDataAdapter1.Fill(DataSet11)
    End Sub
End Class


This results in the system error as soon as the page loads.  The SQL server is SQL 2005.  The version of VB.NET is 1.1 sp1
Please advise.

try a simpler query like this:
Me.SqlSelectCommand1.CommandText = "SELECT STATUS, SCHOOLNUM, LASTNAME, FIRSTNAME FROM SASI.ADST WHERE (STATUS = '')"

That was actually not the problem.  The problem involved saving the password for the connection.  Do you know how to save it so that it is not readable in compiled sourcecode?

Here's a hint, go grab yourself a copy of vs2005 express and run it with .net 2.0 all your problems will magically dissapear.  Then you can encrypt your web.config which stores the passwords for connection strings which have saved me alot of time :)

It will if you view the source code in a hex editor...  Unfortunately for this app I have to do it in 1.1 so that's the way it goes 8(  

I am still having problems as my dataset will not create an update statement which I am confused about also...

Hex Editor thats true, i guess never really thought of that since i encrypt all my connection strings anyway > :)

I have some old projects in 1.1, and i mean some really old ones but with some basic adapters i used to specify the parameters like this since your doing a Fill im under the indication that you have already set the parameters.


'----------- Porition of Code
   
        Dim dtScanner As DataTable
        Dim dtScannerRow As DataRow

        dtScanner = Scannerds1.Tables("tblWebOrders")

        SCANDA.SelectCommand.Parameters(0).Value = "True"
        Scannerds1.Tables("tblWebOrders").Clear()
        SCANDA.Fill(Scannerds1, "tblWebOrders")

       
'---------------------------

This code was just something quick and simple it sets a parameter to a value, up in your code you have parameters being used.

You need to set each one, since i haven't seen any code where you have set these values for your select command, so incase you haven't set your parameters hope that helps.

What I am not understanding is when I set up a SQLDataAdapter and attempt to configure it it will not generate the update, insert, or delete code it returns an error.  The user for now is sa so permissions on the sql2005 database are not the issue.  Why is .NET refusing to generate the above functions?

Just for testing sake, im about to go into a meeting could you try using a OLE adapter.  I should be back in about 2 hrs or so.

One thought though, is there a primary key in the table?  Usually tables will not allow generation of updates if there is no primary key.

Im back briefly,

Had a chance to try the ole or check the primary key?

Hi

As darkpriest  states you need to define primary key.

Here is a small simplyfied ole code you can test out ! allthough if implemented in a larger solution then watch the scope for the commandbuilder      


        Dim pk(0) As DataColumn'define a primarykey collum
        Dim  cbSqlDataAdapter1 As OleDb.OleDbCommandBuilder 'define the command builder
        Dim  sId As Integer 'variabel to hold the found primarykey in the dataset
        Dim  sSql As String
        Dim  TmpStr As String
        Dim  oDatarow As DataRow
-----------------------------------------------------

        m_cn.Open() 'open the connection
        DataSet11.Clear()

        'fill the dataset and setup the dataadapter
        sSql = "SELECT Priority,Id,Firmname,Mailaddress,Fname,Lname,Katagori FROM tblname"
        SqlDataAdapter1.SelectCommand = New OleDb.OleDbCommand(sSql, m_cn)
        SqlDataAdapter1.Fill(DataSet11, "tblname")

        DataSet11.Tables("tblname").Columns("Id").AutoIncrement = True
        pk(0) = DataSet11.Tables("tblname").Columns("Id")   '<--- the name collumname of your primary key
        DataSet11.Tables("tblname").PrimaryKey = pk
        m_cn.Close()

 ----------------------------------------------------  
       ' do the UPDATE to the datasource ! all the modifications made to the dataset   "insert , update , delete"

        Public Sub updatetblname()
        cbSqlDataAdapter1 = New OleDb.OleDbCommandBuilder(SqlDataAdapter1)
        SqlDataAdapter1.Update(DataSet11, "tblname")

        End Sub

--------------------------------------------------------

Example ...

       'loop through the dataset collum (6) to see if a match is found to conditon = TmpStr

       TmpStr = "some condition"

        For Each oDatarow In DataSet11.Tables("tblname").Rows

            If oDatarow.Item(6) = TmpStr Then
                sId = oDatarow.Item(1)'the collum with primary
                DataSet11.Tables("tblname").Rows.Find(sId)' find the particular row with that primary
                oDatarow.Delete()
            End If
        Next

        updatetblname()' call the update sub
----------------------------------------------------
vbturbo

I guess I should state the entire scope of what I am doing.  I am working against a database that CANNOT be modified in any way.  If there is no primary key, that is how it must remain and in the table in question that is true.  So if I hand code the connection etc is that still using ADO.NET?  I thought the point of .NET was to ease database programming by allowing you to drag components to the designer..  If I have to hand code it all that's fine but it seems to me that .NET is not all it is cracked up to be.  If I hand code this (Even without a primary key) I will successfully be able to add and update records so why won't the designer just warn about it, but still create the code?  

Hi

I belive that when using the designer to generate your " update delete " statements it would need a primary key to create such statements/methods for you,
although it can create "select and insert " statements under your mentioned problem description.
But without an unique identifier (pk) you wount be able to acomplish that.
It would be fair to say that , if there is a ambiguos possibility then the designer wount create your desired request. Such thing doesn't exist in any progamming langues.

And yes off course you hand write code to work around/acomplish that, what the designer wount do for you.
Also you seem to know that designers are a bit limited in some regards.

I personally belive there is benefits and disadvantece useing ado.net "most benefits"
The greatest one in my humble opinion is useing dataset's , you can make any manipulation to without makeing/addressing a connection request to the database
until you are done with all your data manipulation and then in the END, do one final request to the database namely ...updateing all the modifications you made
to the dataset


Answer to your question
>>>so why won't the designer just warn about it, but still create the code?  
As mentioned above it creates the insert and select methods for you and you would also have seen 2 warnings (yellow once)
SO that why the designer still creates the code for you.(thogh it is limited)

Well with that said I will just hand code the connections etc and not drag and drop.  Thanks for your input!

-D-

what vbturbo said ;)