Error in Creating Dataset

Results 1 to 2 of 2

Thread: Error in Creating Dataset

  1. #1
    Join Date
    Dec 1969

    Default Error in Creating Dataset

    Can you, please, tell me what my problem is here? I&#039;m trying to load into memory some data from 2 related tables in an Access database so that they can be displayed one record at a time. It&#039;s my understanding that, for best performance, I should save the data in a DataTable & access it thru a DataView. Am I reading this correctly?<BR><BR>I&#039;m getting the following error message: "Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters." The line that&#039;s highlighted is the one reading: "dadCompatibility.Fill(dstCompatibility, "Traits")"<BR>Here&#039;s the code:<BR><BR>Sub Page_Load(sender as Object, e as EventArgs)<BR> Dim intTraitNo as Integer, _<BR> intQuestNo as Integer, _<BR> intFindIndex as Integer<BR><BR> Dim dstCompatibility as System.Data.DataSet, _<BR> conCompatibility as System.Data.OleDb.OleDbConnection, _<BR> dadCompatibility as System.Data.OleDb.OleDbDataAdapter, _<BR> dtblTraits as System.Data.DataTable, _<BR> dtblQuestions as System.Data.DataTable, _<BR> dvwTraits as System.Data.DataView, _<BR> dvwQuestions as System.Data.DataView, _<BR> arrQuestKey(1) as Object<BR><BR> Dim connectionString as String = "Provider=Microsoft.Jet.OLEDB.4.0; " _<BR> & "Ole DB Services=-4; Data Source=C:My ASPNet Web SiteCompatibility.mdb"<BR><BR> intTraitNo = Val (lblTraitNo.Text)<BR> intQuestNo = Val(lblQuestNo.Text)<BR> &#039;Is cached "Traits" dataset available?<BR> dvwTraits = Cache ("Traits")<BR> dvwQuestions = Cache("Questions")<BR> If (dvwTraits Is Nothing) Or (dvwQuestions Is Nothing) Then<BR><BR> &#039;Retrieve Traits & Questions from database<BR> dstCompatibility = New System.Data.DataSet()<BR> conCompatibility = New System.Data.OleDb.OleDbConnection(connectionString )<BR><BR> dadCompatibility = New System.Data.OleDb.OleDbDataAdapter( _<BR> "SELECT [tbl_traits].[trait_no], [tbl_traits].[trait_desc] " _<BR> & "FROM [tbl_traits] WHERE ([tbl_traits].[trait_no] &#062;= intTraitNo)", conCompatibility)<BR> conCompatibility.Open()<BR> dadCompatibility.Fill(dstCompatibility, "Traits")<BR> dadCompatibility.SelectCommand = New System.Data.OleDb.OleDbCommand( _<BR> "SELECT [tbl_questions].[trait_no], [tbl_questions].[quest_no], " _<BR> & "[tbl_questions].[question] FROM [tbl_questions] WHERE " _<BR> & "([tbl_questions].[trait_no] &#062;= intTraitNo)", conCompatibility)<BR> dadCompatibility.Fill(dstCompatibility, "Questions")<BR> conCompatibility.Close()<BR><BR> &#039;Add parent/child relationship<BR> dstCompatibility.Relations.Add("Traits_Questions", _<BR> dstCompatibility.Tables("Traits").Columns("trait_n o"), _<BR> dstCompatibility.Tables("Questions").Columns("trai t_no"))<BR><BR> &#039;Create datatables<BR> dtblTraits = New System.Data.DataTable("Traits")<BR> dtblTraits.MinimumCapacity = 13<BR> dtblQuestions = New System.Data.DataTable("Questions")<BR> dtblQuestions.MinimumCapacity = 265<BR><BR> &#039;Create dataviews<BR> dvwTraits = dtblTraits.DefaultView<BR> dvwQuestions = dtblQuestions.DefaultView<BR><BR> &#039;Store dataviews in memory<BR> Cache ("Traits") = dvwTraits<BR> Cache ("Questions") = dvwQuestions<BR> End If<BR><BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Several things wrong..

    For starters, your SQL queries are bogus. Because you are doing<BR> WHERE [tbl_questions].[trait_no] &#062;= intTraitNo<BR>but there is no intTrainNo field in your table, so kablooey.<BR><BR>What you *want* is the *VALUE* of the VB variable intTraitNo in that query. So you either need to make it a parameter, by doing<BR> WHERE [tbl_questions].[trait_no] &#062;= @intTraitNo<BR>and then creating a parameter and giving it the right type and value and...well, a bunch of work.<BR><BR>*OR* you could *substitute* the VALUE of the VB variable intTraitNo into the string.<BR><BR>Also, there is no reason to (a) put [...] around table and field names unless the name is a keyword or (b) use a table name in front of each field name when you are only using a single table.<BR><BR>*******<BR>Dim SQL As String<BR>SQL = "SELECT trait_no, trait_desc FROM tbl_traits WHERE trait_no &#062;= " & CStr(intTraitNo)<BR>dadCompatibility = New System.Data.OleDb.OleDbDataAdapter(SQL,conCompatib ility)<BR><BR>conCompatibility.Open()<BR>dadCompat ibility.Fill(dstCompatibility, "Traits")<BR><BR>SQL = "SELECT trait_no, quest_no, question FROM tbl_questions WHERE trait_no &#062;= " & CStr(intTraitNo)<BR>dadCompatibility.SelectCommand = New System.Data.OleDb.OleDbCommand(SQL, conCompatibility)<BR><BR>dadCompatibility.Fill(dst Compatibility, "Questions")<BR>conCompatibility.Close()<BR>****** *******<BR><BR>Then the other big problem I see is that you use your queries to FILL a couple of tables in your dstCompatibility DataSet but then you create *separate* tables (dtblTraits and dtblQuestions), put NO DATA AT ALL into them, and create your dataviews on those empty tables. HUH???<BR><BR>I don&#039;t get that, at all. Why not use the tables you just FILLed????<BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts