[RESOLVED] All I need to do is populate my form....

[chr1spy1]

OK, so I've read http://www.vbforums.com/showthread.php?t=469872 15 times trying to find the answers. I have modified the code, and have come to the conclusion that I'm stumped.

I have a form in VB 2005 with a number of fields:

PhoneNumber
Name
Address
CustomerID
etc.

Which correspond to the fields in my backend Access 2003 Database
Cust_PhNumber
Cust_Name
Cust_Address
Cust_ID
etc.

I can do the Insert into my Database no problem. What I'm trying to do is the user will put in a CustomerID in the VB form, and the rest of the data will then populate on the form; it can either be updated, deleted, or left alone. When the user hits the save button, it will update the database acccordingly.

As I mentioned, jmcilhinney's post is really good, so I'm sure the Update portion will not be a problem (although my confidence is definitely wavering). I think a DataGrid would have been easier, but at the time I was designing the forms in Visio, it didn't make sense to have a datagrid that's going to be one row and 32 columns wide.

Any ideas?? Appreciate your suggestions...
Thanks
Chris

[BackWoodsCoder]

So basically what you want to do is return a result set based upon the ID your user enters?

You need to write a select query that brings back the results you want, populate a dataset with those results, write a for loop to go through the dataset and populate your controls with the data.

[BackWoodsCoder]

try something like this

Dim sqlDA as New SqlDataAdapter
Dim DS as New DataSet
Dim SelectCmd as New SqlCommand
 
SelectCmd.CommandText = "SELECT Cust_PhNumber, Cust_Name, Cust_Address, Cust_ID FROM CustTable WHERE CustID = @CustID" ''obviously change cust table to whatever table you're using
SqlDA.SelectCommand = SelectCmd
SqlDa.SelectCommand.Connection = *Whatever you're using for a sqlConnection*
SqlDa.SelectCommand.Parameters.Clear()
SqlDa.SelectCommand.Parameters.AddWithValue("@CustID", txtcustid.text) ''insert the control you're using to grab the custid for txtcustid.text
 
SqlDa.fill(DS)
 
For Each Row As DataRow in DS.tables(0).rows
txtname.text = Row("Name")
txtaddress.text = Row("Address")
etc 
Next

one way to do it anyhow.

[jmcilhinney]

As you can see from BackWoodsCoder's example, using a parameter in a SELECT statement is exactly the same as using a parameter in a DELETE, INSERT or UPDATE statement. In fact, using parameters is ALWAYS the same regardless of the contents of the SQL code you're executing. The first five examples in my thread show you how to retrieve data and the sixth example shows you how to use parameters. I guess I just assumed that people would put the two together but you're far from the first to not, so maybe I'll add another example that does just that.

[chr1spy1]

Hi Backwoodscoder and everyone else,

OK, so I have taken the code as suggested by Backwoodscoder and have modified it with my info; i've also changed the Connection String to be consistent with the rest of the application.

I realise this is a fairly straightforward piece of code, and I'm kind of smart, but I cannot figure out why nothing happens. I don't get any errors generated, but I put my CustomerID in the text field, hit tab, and....nothing.

Any ideas? I read an online blog saying you need to use ExecuteDataReader but I don't know if that's required here........
Appreciate your thoughts...
Thanks
Chris

Private Sub Srv_CGITicket_LostFocusHandler(ByVal sender As Object, ByVal e As System.EventArgs)
Dim sqlDA As New SqlClient.SqlDataAdapter
Dim DS As New DataSet
Dim SelectCmd As New SqlClient.SqlCommand

SelectCmd.CommandText = "SELECT Cust_name,Cust_PhoneNumber FROM Cust_Info WHERE CustID = @CustomerID"
sqlDA.SelectCommand = SelectCmd
Using connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\...\CustomerUtility.mdb")
sqlDA.SelectCommand.Parameters.Clear()
sqlDA.SelectCommand.Parameters.AddWithValue("@CustomerID", CustomerID.Text)
sqlDA.Fill(DS)
For Each Row As DataRow In DS.Tables(0).Rows
Me.Srv_DateTimePicker1.Text = Row("Cust_Name")
Me.Srv_Details.Text = Row("Cust_PhoneNumber")
Next
End Using
End Sub
End Class

[chris128]

Have you actually put a breakpoint in your code and verified that any of it is even being run at all?

[chr1spy1]

......not entirely sure why I didn't think of that...

No, in fact my code is not running at all. Now I'm really confused. Any way I can download VB2005 into my brain so I can stop pestering you nice people? :-)

[chris128]

How did you hook up the event handler? I notice it doesnt have the Handles XXXX.LostFocus after it so I'm assuming you have used AddHandler somewhere?

[chr1spy1]

hee hee hee ummmmmm I'm guessing this shows my total lack of experience with VB2005. No, i did not have the Handles portion of my Sub. Didn't even see that I had missed it, so thank you for showing me that.

OK, so now at least my code is doing something - and then it stops. I'm now getting an error at the row "SQLDA.Fill(DS)".

Error is "{"Fill: SelectCommand.Connection property has not been initialized."}"

I've tried moving the SelectCmd.CommandText after the connectionString, but still get the same error.
Appreciate your time...

[chris128]

hee hee hee ummmmmm I'm guessing this shows my total lack of experience with VB2005. No, i did not have the Handles portion of my Sub. Didn't even see that I had missed it, so thank you for showing me that.

One easy way to avoid mistakes like that is to let Visual Studio do it for you. When you are in code view, if you look at the drop down boxes at the top of the code page you should see one on the left that lets you pick an object/control and one of the right that shows you all of the events that the control you selected in the left drop down box has. If you select one of these events then VS will create the event handler for you

As for your error, it is because as the error says - you have not set the Connection property. So simply tell it that you want it to use the OledbConnection object that you created like so:

SelectCmd.Connection = connection

One thing I have just noticed though is that you are mixing the SQLClient and OleDb classes together - I dont know if that will work. I've only ever used the SQLClient classes myself but I assume there is an OleDb equivalent of SqlCommand and SqlDataAdapter etc that you should be using if you are working with Access

[chr1spy1]

OK, this is all starting to make sense. Thanks Chris128 for your time.

Now, back to my issue

I've updated the SQL stuff to OleDB. There are comparitive selections for the OleDB as there were for the SQL, so changing them was not a big deal.

When I updated my code and rearranged the order slightly (I was getting an error for Connection that said I was using it before it was established), my new code looks like this:

[quote]
Using connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\...\..\.mdb")
SelectCmd.Connection = connection
SelectCmd.CommandText = "SELECT ......"
DA.SelectCommand = SelectCmd
DA.SelectCommand.Parameters.Clear()
DA.SelectCommand.Parameters.AddWithValue("@CustID", CustomerID)
DA.Fill(DS)
For Each Row As DataRow In DS.Tables(0).Rows
Me.Srv_DateTimePicker1.Text = Row("Cust_PhoneNumber")
Me.Srv_Details.Text = Row("Cust_Address")
Me.Srv_Details.Text = Row("Cust_Details")
Next
End Using
[\quote]

I get a "Variable 'Connection' hides a variable in an Enclosing block" for the "Using connection..." statement. So I comment out the "Using connection..." and the "End Using", and the application runs, seems to query the data, but then it stops at the row Da.Fill(DS), with the error "{"Fill: SelectCommand.Connection property has not been initialized."}" once again. I'm also noticing now that my SelectCMD.Connection = connection has generated a warning, saying "Variable 'connection' is used before it has been assigned a value..." which makes sense as I haven't specified a Connection string. So, I am assuming the "Using..End Using" is the way to go, I just cannot figure out how to correct the error.
Why is it I had less problems with Inserting than I do with a simple Select??? Argh....

[BackWoodsCoder]

Using blocks are nice I suppose, but have you tried just Dim'ming out the connection as an object?

 Dim connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\...\..\.mdb")

[chr1spy1]

Hey BackWoodsCoder,

Only reason I did it that way is because that's how jmcilhinney's thread did it, and I'm not nearly experienced enough to "see" other ways of doing it...

That being said, I've now tried your idea and am still getting an error on the DA.Fill(DS) row, "{"No value given for one or more required parameters."}".

I'm reading through my code (all 15 lines of it LOL) very carefully, and I'm really hoping to catch this error myself. Ideas of course would be appreciated.....
Chris

[chris128]

Only reason I did it that way is because that's how jmcilhinney's thread did it, and I'm not nearly experienced enough to "see" other ways of doing it...

All the "Using" block does is takes care of releasing any resources that your object may have been using. So doing this:

Using connection As New OleDbConnection("etc etc")
       'use the connection object here
       '....
End Using

is the same as doing this:

Dim connection As New OleDbConnection("etc etc")
'use the connection object here
'....
connection.Dispose

As for your current error, can you post your actual Select statement?

[BackWoodsCoder]

DA.SelectCommand.Parameters.AddWithValue("@CustID", CustomerID)

put a breakpoint on that line, hover your mouse or use the immediate window to find out what value CustomerID is. I'm guessing this is where your problem is coming from.

[chr1spy1]

Hi Chris128,

Here's the actual select statement; the Customer Info was used to keep the issue generic, but I suppose I could have done it this way with the same effect.

[code] SelectCmd.CommandText = "SELECT reported_user, reported_Support, outage_details FROM tOutagesServer WHERE Srv_Ticket = @Srv_Ticket"[\code]

The columns in the Select are from my database, the tOutagesServer is my Access table name, and the Srv_Ticket is the text box name on my form.

[chr1spy1]

DA.SelectCommand.Parameters.AddWithValue("@CustID", CustomerID)

put a breakpoint on that line, hover your mouse or use the immediate window to find out what value CustomerID is. I'm guessing this is where your problem is coming from.

Just tried that, it's passing the correct information....I step through and then the next line, DA.Fill(DS) kicks out the error....
Thanks
Chris

[chris128]

and you are sure that in your real code it looks EXACTLY like this (the only difference being that the DATA_HERE would be something else obviously:

DA.SelectCommand.Parameters.AddWithValue("@Srv_Ticket", DATA_HERE)

Oh and you can also try adding the parameters in a different way, something like this:

DA.SelectCommand.Parameters.Add("@Srv_Ticket", OLEDB_DATA_TYPE_HERE).Value = DATA_HERE

[chr1spy1]

Hi Chris128,

Yup, absolutely the same - DA.SelectCommand.Parameters.AddWithValue("@Srv_Ticket", Srv_Ticket.Text). I've also tried your second suggestion, and once again it's passing my parameter but getting stuck on the DA.Fill(DS) line.

[BackWoodsCoder]

just for grins...

DA.SelectCommand.Parameters.Clear()

comment out that line and run the code and see what happens.

[chris128]

is it still saying the exact same thing in the error message? No value given for one or more required parameters

[chr1spy1]

Hi BackWoodsCoder / Chris128,

I commented out the .Parameters.Clear() line, and am still getting the error; and yup, it's definitely No Value Given for one or more required Parameters...

Is it me or is this getting annoying and frustrating?

[chris128]

Can you post your entire method again but exactly as it is now, only changing things that would affect security (e.g usernames and passwords in the connection string)

[chr1spy1]

Hi chris128,

Here you go...

Private Sub Srv_Ticket_LostFocusHandler(ByVal sender As Object, ByVal e As System.EventArgs) Handles Srv_Ticket.LostFocus
Dim DA As New OleDb.OleDbDataAdapter
Dim DS As New DataSet
Dim SelectCmd As New OleDb.OleDbCommand
Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\....\Notifications.mdb")
SelectCmd.Connection = connection
SelectCmd.CommandText = "SELECT reported_user, reported_support, outage_details FROM tOutagesServer WHERE Srv_Ticket = @Srv_Ticket"
DA.SelectCommand = SelectCmd
DA.SelectCommand.Parameters.Clear()
DA.SelectCommand.Parameters.AddWithValue("@Srv_Ticket", Srv_Ticket.Text)
DA.Fill(DS)
For Each Row As DataRow In DS.Tables(0).Rows
Me.Srv_User.Checked = Row("Reported_User")
Me.Srv_Support.Checked = Row("Reported_Support")
Me.Srv_Details.Text = Row("Outage_Details")
Next
End Sub

[BackWoodsCoder]

Ok, let's do a little breakpoint investigation using your immediate window.

DA.Fill(DS)

put a break point on that line, execute your code, when your program breaks go to the immediate window and type in the following

?DA.SelectCommand.Parameters.Count

Then look at the count, if the count is greater then 1 somehow somewhere another parameter is being added to the query. If it is one, go back to the immediate window and type in

?DA.SelectCommand.Parameters(0).Value

this will tell you the value the parameter is holding. If it is nothing there's your problem, if not it lies somewhere else.

[chris128]

I'm stumped!

[chr1spy1]

Hi BackwoodsCoder,

this is what the immediate window's returning

"123455" {String}
String: "123455"

123455 is the parameter I'm passing.

[chr1spy1]

I'm stumped!

Crap. This doesn't bode well for me then LOL.

Thanks for your time Chris128, it's very much appreciated. Any other ideas are more than welcome...

[chris128]

What about when you do the ?DA.SelectCommand.Parameters.Count in the immediate window, what did that show?

[BackWoodsCoder]

So I'm assuming the count of your parameter collection was only one?

This should not matter at all, but if your datatype for Srv_Ticket in your DB is int, and you're passing in a string, it may not like it.

So, modify this line to read like this.

DA.SelectCommand.Parameters.AddWithValue("@Srv_Ticket", CInt(Srv_Ticket.Text))

This will convert the string into an integer, it really shouldn't matter but it's worth a shot.

[chr1spy1]

Hi BackWoodsCoder,

yes, a 1 was returned in the immediate window....should have clarified that...

I've just double checked my database and the field is "text". would that make a difference? Should I still try doing the conversion to Int?

[BackWoodsCoder]

no, if you have a text datatype in the DB, then you'll want to pass a string to it.

Ok, lets try a different tack here for a minute and lets see if we can get the code working. Then we can muck around with parameters. We'll do it the no no way and see if it works.

Comment out the lines dealing with parameters, remove the parameter from your SQL string, and replace the sql string line with this.

SelectCmd.CommandText = "SELECT reported_user, reported_support, outage_details FROM tOutagesServer WHERE Srv_Ticket = '" & SRV_Ticket.Text & "'"

Now this is a bad way to do it because you open yourself to sql injection. But, lets start with getting the code to work and we'll work our way back up to doing it correctly.

[chr1spy1]

Edit - Just scrolled over and I see you've concantenated the VB with the Text line - i didn't scroll over far enough. I'll do that now, but given I had removed ALL indications of a parameter and I'm still getting the error message, I feel that's significant (but how I have no clue). Now I know what House feels like.....

Done. And I'm still getting stuck on the DA.Fill(DS) line, with the same error ("{"No value given for one or more required parameters."}").

I've remm'd out the Select statement and replaced it with a straight "select reported_user, reported_support, outage_details from toutagesserver", and i've also remm'd out my DA.SelectCommand.Parameters.AddwithValue("@Srv_ticket","Srv_Ticket.txt") and the DA.SelectCommand.Parameters.Clear().

OK, so I just tried your concantenated VB and still get the error. However, (and I really hope this has nothing to do with it), the Subroutine I've got just above this one has three parameters, @Srv_Ticket, @Srv_DateTimeNotified, and @Srv_SupportRequested. I've remm'd out the entire routine and still get the error, so I'm thinking not, but hey, what do I know LOL

OK, *now* I'm going for lunch. Thank God I brought pasta, I'm going to need the carbs to survive this aft!!

[BackWoodsCoder]

ok, hrm, this is getting frustrating. Lets try and do this the long winded way of adding a parameter

Dim SRV_Ticket As New OleDb.OleDbParameter("SRV_Ticket_Parameter", OleDb.OleDbType.Char) 

SRV_Ticket.Value = SRV_Ticket.Text

DA.Parameters.Add(SRV_Ticket)

Had to do a little work because i never use access as a DB, so I figured there might be some minute differences between the two. You can read more about what i posted here

http://social.msdn.microsoft.com/for...f-11cbf934e0b0

Although he is asking about executing multiple queries, the logic behind getting it setup is what you need to know.

[chr1spy1]

OK, this is getting stupid. Still no luck, still getting stuck at the same line.

I'm thinking of clearing this entire subroutine and re-write it from scratch. I doubt that will make any difference but hey, you never know.

I also tried removing the LostFocus method and putting it to a button, desperate to try anything. Still nothing.

And people don't believe me when i say I spend 7 hours on one single line of code LOL

[BackWoodsCoder]

http://www.vbforums.com/showthread.php?t=587638

Maybe that thread will help?

[chr1spy1]

*sigh*...i've put a single quote around everything I could think of, and I'm still getting stuck on this one single @%&*!!! line of code; everything else runs fine, either with or without the quotes. Argh. I'm off on vacation next week and I need to have this app finished by Thursday afternoon. Not going to happen if I continue to run into problems like this. I'm thinking I put this particular form on hold for now and come back to it later.
Thanks BackwoodsCoder and Chris128, I really appreciate all your help. I'm going home shortly but will be logged back in shortly after dinner, so please - any far-fetched, potentially insane ideas are most welcome.
Thanks again....
Chris

[dminder]

Are you ready to kick yourself? You forgot a ";" at the end of your connection string. You MUST put that after each section of your connection string. It should look like this:

Dim connection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\....\Notifications.mdb;")

I tested it and had no problems with it working. HOWEVER....if you get the same message, check (this means copy and paste) your column names between the database and the code in your app.

*I hope that I am correct otherwise... *

D

[BackWoodsCoder]

Wait a minute....I think I may have found the problem....

http://social.msdn.microsoft.com/For...f-8604d335c776

Are you sure the column names you're querying are correct?

and lastly (from that thread)

Yes, that error message has nothing to do with the usage of a reserved words. The use of reserved words will generate a syntax error.

When using OLEDB with ADO.NET named parameters are unsupported. Instead, remove the parameter names from your SQL statement and replace each with a question mark. In addition, you can remove the parameter names when adding parameters to the Parameter collection. Just make certain to add them to the collection in the order which corresponds to their order in the SQL statement.

As I mentioned in my previous post, you may want to dump out the resulting SQL statement (strSQL) and post it here so we can take a look at it.

[chris128]

When using OLEDB with ADO.NET named parameters are unsupported

That is naff... why the hell does the OleDbCommand let you add named parameters if that is the case!