2

I have created a SQL Database with a VB application. I have got a form that allows users to insert new records. Each new record has a unique ID number. I want the user to be able to search for a record using the ID number.

I have got one page with a text box that allows them to insert their ID number. Then they click submit. So the database is queried; if the ID number does not exist they get a message box with an error. If the ID number does exist a new page is displayed and the record is displayed.

I assume the database connection would begin on the first page when the user inputs their ID number. I have got two tables for orders so I need to query both tables. This is the code I have for the first page.

    Try

        Dim dbconnection As SqlConnection = New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Cara\Documents\Visual Studio 2012\Projects\Online Portal Solutions\Online Portal Solutions\Online Portal Solutions Database.mdf;Integrated Security=True")
        dbconnection.Open()
        Dim statement As String = "SELECT * FROM [JKPOrders] WHERE OrderNoID='" & txt_jkpfind.Text & "';"
        Dim com As SqlCommand = New SqlCommand(statement, dbconnection)
        Dim read As SqlDataReader = com.ExecuteReader

        Dim dbconnection2 As SqlConnection = New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Cara\Documents\Visual Studio 2012\Projects\Online Portal Solutions\Online Portal Solutions\Online Portal Solutions Database.mdf;Integrated Security=True")
        dbconnection2.Open()
        Dim statement2 As String = "SELECT * FROM [ClarkeOrders] WHERE OrderNoID='" & txt_jkpfind.Text & "';"
        Dim com2 As SqlCommand = New SqlCommand(statement2, dbconnection2)
        Dim read2 As SqlDataReader = com2.ExecuteReader

        If read.Read Then
            If txt_jkpfind.Text.ToString <> read("OrderNoID") Then
                jkpfindorderno = Val(txt_jkpfind.Text)
                Me.Hide()
                frm_Ecustjkpbookingsummary.Show()
            End If

        ElseIf read2.Read Then
            If txt_jkpfind.Text.ToString <> read("OrderNoID") Then
                jkpfindorderno = Val(txt_jkpfind.Text)
                Me.Hide()
                frm_Ecustjkpbookingsummary.Show()
            End If

        Else
            MessageBox.Show("no.", "No Entry",
                       MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

    Catch ex As Exception

    End Try

It identifies when a record does not exist and displays the message box but when the right ID number is input it doesn't do anything, what am I doing wrong?

Also, I have got a data adapter, data binder, etc on the following page that displays the record. Is this how I display the record and how do I do that?

Improve this question
6
  • A few things. 1) Store your connection strings in your web.config instead of hardcoding them (msdn.microsoft.com/en-us/library/ms178411.aspx). 2) Use paramaterized queries to avoid a user trying to insert an executable SQL statement (msdn.microsoft.com/en-us/library/…). Commented Feb 3, 2014 at 14:38
  • Thanks for your help. But that doesn't really answer my question. I have gone too far now to start changing things. Just really need to get it to work :) Commented Feb 3, 2014 at 14:44
  • I know it wasn't an answer to your question, which is why I didn't post it as an answer. I'm posting it to guide you towards better coding practices, either for this code or for code you write in the future. Even then it's a little hard to grep what you're asking as it appears you haven't posted all relevant code, and even then you can't seem to state very simply what it is you're trying to do. To that end your If/ElseIf/Else setup doesn't make much sense to me. Lastly, you never close your DB connections when you're done with them, unless that's in another part of the code. Commented Feb 3, 2014 at 14:51
  • Thanks for your help, I am awful at coding and I am only doing it because I have to. I know my code isn't best practice, I have been at uni for 4 years n they haven't taught us enough (clearly). I just need some help to get my project working. I have tried to include as much info as possible without rambling. I can provide further information when requested. I have just been writing it to work rather than following good guidelines. I am sorry if this offends you, but I just need to get it to work. Commented Feb 3, 2014 at 15:08
  • No, I get it, but basically if I hadn't posted what I did, someone else would have. :P Commented Feb 3, 2014 at 15:10

2 Answers 2

Reset to default
1

Having two connections is unnecessary. You're using a mdf database file. Which is an access database file. I'm pretty certain Access only allows one connection at a time. So you're first query might be blocking the second one. See if it works by just creating one connection.

Also, all your database connections are local to this function and have nothing to do with your data bindings. You will have to show details about how you have your binding setup for us to point out what might be wrong with it.

Also, you should use paramaterized queries to prevent sql injection and other things. To parameterize the first query do this.

Dim statement As String = "SELECT * FROM [JKPOrders] WHERE OrderNoID=@OrderId;"
Dim com As SqlCommand = New SqlCommand(statement, dbconnection)
com.Parameters.Add(new SqlParameter("@OrderId", txt_jkpfind.Text))
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Yeah, the two connections are unnecessary but it does work. I have it working for a log in feature. I 100% know that the sql connections are fine and the statements, commands etc. I haven't set up data bindings for this part yet just in case I did it wrong. So if I use your code above with a datareader it will query the database and continue if right and stop if wrong? How do I get the record details to display on the next page?
Well, I don't know what you have on the next page (or what the next page is). So I can't suggest what changes to make. You could read data from your database into text or label fields. LabelSomeField.Text = CStr(read("FieldFromYourDb")). You mentioned you are using data adaptors. Maybe this will help, msdn.microsoft.com/en-us/library/bh8kx08z(v=vs.110).aspx
Also, if all you're concerned about with the parameter addition is the parameter name and value associated with it, you can simplify the third line of your code sample to just be com.Parameters.AddWithValue("@OrderId", txt_jkpfind.Text). Details here: msdn.microsoft.com/en-us/library/…
Oh, your if condition hides the current form and then shows another form if the id from the database is not equal to what the user typed. If you are querying based on what the user typed this will never happen. Maybe you meant to use = not <>.
Thank you for all your help guys :) I will try and have a go with all your ideas. Just to answer your questions the first page is a text box with a submit button. The user enters the order number presses submit and then the results are displayed on the next page with hide.show. I don't mind how the results are displayed so if labels are easier can you tell me how to do them. Any further help is really appreciated.
|
0

Try like this

 If read.Read Then
            If txt_jkpfind.Text.ToString <> read("OrderNoID").ToString() Then
                jkpfindorderno = Val(txt_jkpfind.Text)
                Me.Hide()
                frm_Ecustjkpbookingsummary.Show()
            End If

        ElseIf read2.Read Then
            If txt_jkpfind.Text.ToString <> read("OrderNoID").ToString() Then
                jkpfindorderno = Val(txt_jkpfind.Text)
                Me.Hide()
                frm_Ecustjkpbookingsummary.Show()
            End If

        Else
            MessageBox.Show("no.", "No Entry",
                       MessageBoxButtons.OK, MessageBoxIcon.Error)
 End If
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.