Results 1 to 10 of 10

Thread: Dataset help! [RESOLVED]

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2004
    Location
    Utah, USA
    Posts
    353

    Dataset help! [RESOLVED]

    I have never used a Dataset before. I was using just a datareader to do everything on my program I am designing for work. I was told it is much better to use a dataset, so now I am trying to revamp my whole program. Crazy huh? Anyway, right now I have two forms. In Form2 I have a textbox and a Search button. Here is my coding for the Search button so far:

    VB Code:
    1. Dim SqlConn As New SqlConnection("data source=crserver;initial catalog=crdatabase;integrated security=SSPI;persist security info=False;workstation id=CRSERVER;packet size=4096")
    2.         Dim SqlDataAdapter1 As New SqlDataAdapter()
    3.         If txtSearch.Text.Length = 0 Then
    4.             MessageBox.Show("Please Enter a Case Number or Last Name!", "Message Box", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    5.             txtSearch.Focus()
    6.             SqlConn.Close()
    7.             Exit Sub
    8.         ElseIf IsNumeric(txtSearch.Text.Substring(0, 1)) = True Then
    9.             SqlDataAdapter1.SelectCommand = "SELECT * FROM tblCapRec WHERE CaseNumber LIKE '" & txtSearch.Text & "%' ORDER BY CaseNumber, PartNumber, LastName, FirstName"
    10.             'Debug.WriteLine(cmdSqlCommand1.CommandText)
    11.         Else
    12.             SqlDataAdapter1.SelectCommand = "SELECT * FROM tblCapRec WHERE LastName LIKE '" & txtSearch.Text & "%' ORDER BY LastName, FirstName, CaseNumber, PartNumber"
    13.         End If
    14.         SqlDataAdapter1.SelectCommand.Connection = SqlConn
    15.         SqlConn.Open()
    16.  
    17.         SqlDataAdapter1.SelectCommand.Parameters("CaseNumber").Value = txtSearch.Text
    18.         DsRecords1.Clear()
    19.         SqlDataAdapter1.Fill(DsRecords1)

    I know that is not coded totaly right, but can anyone help me out. I want to retrun a global variable to Form1 and to display the information according to what I found in Form2. What coding would I have in Form1? I know that is a lot of information I need, but any suggestions would be great. Thanks!

    Brenda
    Last edited by brendalisalowe; Sep 8th, 2004 at 02:52 PM.

  2. #2
    Lively Member nokia8210's Avatar
    Join Date
    Dec 2002
    Location
    Coventry
    Posts
    92
    try setting a variable on form 1 and then on form2 use

    me.parentform.(Variable)= Value

    if not try having the data set on form1 using the me.parentform code to fill the dataset .

    Prob would look something like this:


    SqlDataAdapter1.SelectCommand.Parameters"CaseNumber")
    .Value = txtSearch.Text

    me.ParentForm.DsRecords1.Clear()

    SqlDataAdapter1.Fill(me.ParentForm.DsRecords1)


    i hope this helps if not send me what your trying to do and i'll see if i can help

    Good Luck

  3. #3
    Addicted Member
    Join Date
    Apr 2004
    Location
    Lagos, Nigeria
    Posts
    215
    If the data fetched from the database will be used in form1, then declare your dataset in form1 and fill it in form2.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2004
    Location
    Utah, USA
    Posts
    353
    Here is my file. If you could just lead me in the right direction, that would be great! Thanks!

    Brenda
    Attached Files Attached Files

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2004
    Location
    Utah, USA
    Posts
    353
    So does anyone of any idea of how to at least get me started? I am still confused...which is normal. I have been researching some but still can't figure it out. Do I need a dataset in both forms or just one? Do I pass the values? I have NO idea. Thanks for any help!

    Brenda

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2004
    Location
    Utah, USA
    Posts
    353
    This is what I have now:
    VB Code:
    1. Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
    2.         Dim SqlConn As New SqlConnection("data source=67.40.98.209;initial catalog=crdatabase;integrated security=SSPI;persist security info=False;workstation id=CRSERVER;packet size=4096")
    3.         Dim adapter As New SqlDataAdapter()
    4.         'Test the length first
    5.         If txtSearch.Text.Length = 0 Then
    6.             MessageBox.Show("Please Enter a Case Number or Last Name!", "Message Box", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
    7.             txtSearch.Focus()
    8.             SqlConn.Close()
    9.             Exit Sub
    10.             'Determine if the first character is numeric, if it is search on CaseNumber
    11.         ElseIf IsNumeric(txtSearch.Text.Substring(0, 1)) = True Then
    12.             adapter.SelectCommand = New SqlCommand("SELECT * FROM tblCapRec WHERE CaseNumber LIKE '" & txtSearch.Text & "%' ORDER BY CaseNumber, PartNumber, LastName, FirstName", SqlConn)
    13.             adapter.Fill(DsSearch1)
    14.             'The value is not numeric, search on LastName
    15.         Else
    16.             adapter.SelectCommand = New SqlCommand("SELECT * FROM tblCapRec WHERE LastName LIKE '" & txtSearch.Text & "%' ORDER BY LastName, FirstName, CaseNumber, PartNumber", SqlConn)
    17.             adapter.Fill(DsSearch1)
    18.         End If
    19.         SqlConn.Open()
    20.         [COLOR=red]CaseNumber = DsSearch1.????[/COLOR]
    21.          End Sub

    I don't know if this is correct, but can I set CaseNumber equal to something in the dataset? How? Thanks!

    Brenda

  7. #7
    Addicted Member Rockhopper's Avatar
    Join Date
    Aug 2003
    Location
    Cape Town, South Africa
    Posts
    199
    Hey Brenda...

    I'm not sure about all the filling of the dataset etc. (haven't really oogled the code)


    but if you want to make the CaseNumber equal to something in the dataset...

    Do you know what datatype it is?

    Is it a variable, or a field of the dataset?

    If i understand you correct... to make for example a string = a value in the dataset, and you know the row and column in the table:

    Code:
    Dim CaseNmbr as String
    CaseNmbr = CType(dsSearch.Rows(0).Item("CaseNumber"), String)
    let me know if this helps, or you need more
    Last edited by Rockhopper; Sep 1st, 2004 at 04:18 PM.
    If the facts don't fit the theory, change the facts. --Albert Einstein

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2004
    Location
    Utah, USA
    Posts
    353
    It says "Rows is not a member of CapRec.DsSearch"

    Am I not filling it right? And then how will I pass that to form1 once that works? Thanks for your help!

    Brenda

  9. #9
    Addicted Member Rockhopper's Avatar
    Join Date
    Aug 2003
    Location
    Cape Town, South Africa
    Posts
    199
    A thousand apologies.. the code i should have posted was this...


    Code:
    Dim CaseNmbr as String
    CaseNmbr = CType(dsSearch.Tables("tblCapRec").Rows(0).Item("CaseNumber"), String)


    A dataset is basically a collection of tables. each table has rows... not the dataset itself, that is why the "Rows is not a member of CapRec.DsSearch" appeared.


    sorry for the delay.
    If the facts don't fit the theory, change the facts. --Albert Einstein

  10. #10
    Addicted Member
    Join Date
    Apr 2004
    Location
    Lagos, Nigeria
    Posts
    215
    Hi Brenda, attached is the modified version of the project you posted.

    I've added a form frmSearch2 and two buttons on Form1. The search is performed on form1, the caseNumber and/or lastname is collected from the user using frmsearch2.

    The code is well commented [I hope ]

    I hope this will guid you in the right direction.
    Attached Files Attached Files

Posting Permissions

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



Click Here to Expand Forum to Full Width