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

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

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.

try something like this

vb Code:

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.

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.

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

Quote:

---

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

---

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

......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? :-)

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?

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...

Quote:

---

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:

vb Code:

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

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

Now, back to my issue :D

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....

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

Code:

---

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

---

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

Quote:

---

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:

vb Code:

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

is the same as doing this:

vb Code:

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?

Code:

---

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.

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.

Quote:

---

Originally Posted by BackWoodsCoder

Code:

---

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

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:

vb Code:

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

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

vb Code:

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

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.

just for grins...

Code:

---

DA.SelectCommand.Parameters.Clear()

---

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

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

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? :D

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)

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

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

Code:

---

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

Code:

---

?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

Code:

---

?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.

I'm stumped!

Hi BackwoodsCoder,

this is what the immediate window's returning

"123455" {String}
String: "123455"

123455 is the parameter I'm passing.

Quote:

---

Originally Posted by chris128

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...

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

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.

Code:

---

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.

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?

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.

Code:

---

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.

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!!

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

Code:

---


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.

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

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

Maybe that thread will help?

*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

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:

Code:

---

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... :blush: *

D

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)

Quote:

---

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.

---

Quote:

---

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!

No, you can use parameters in OLEDB, I do it all the time (as I said I tested his code and it passed with no issues, after I added the semi colon to the connection string!). Unlike SQL though, you must add them in the order that they are found in the code. So if you have @Order and then @Number you must put them in that exact order.

would be weird if it was the connection string causing the problem. Usually you would see an error dealing with a connection problem if there as an issue with your connection string.

Of course, I also generated a copy of the database by copying and pasting his column names into the table (from the SELECT statement). If he is still getting the issue after adding the semi colon then most likely he spelled something incorrectly OR the column(s) does not exist. I am pretty much an self proclaimed expert on this by now, I have made that mistake almost every time!! :lol:

D

speaking of connection strings etc I just noticed that there isnt actually a call to Open() on the connection... or dont you have to do this when using DataAdapters etc? I dont normally use them, I normally just open a connection, build a Command, execute it and read the result using a DataReader, then close the connection.

His method is not one that I tend to use either, however the command object does open the connection along the way as it is needed.

D

yep, using data adapters you don't need to open or close the connection. All you need to do is assign a value for the connection property and it handles it for you.

dminder - I loathe Access (well, at least once I started using Oracle/SQL Server), explains why these errors are a bit foreign to me ;)

Quote:

---

I loathe Access (well, at least once I started using Oracle/SQL Server)

---

Join the club! :)

Part of the reason I have gotten pretty good with this is that I have had to fix so many other peoples errors with access and vb..... :sick:

I also like to work with Oracle, MySQL, SQL - when the opportunities afford themselves!!

D

ARRRRRRRRGH GOSH DARN IT.

I thought to myself I should go into my database and see if my test ticket number was written down correctly - it was, but just as I was closing my database, I noticed that someone has gone in AND CHANGED ONE OF MY COLUMN HEADERS. They added three new columns (I orginally had Reported_User; they changed it to Reported_by_User_Phone, Reported_by_User_Email, and Reported_by_User_WalkUp). These columns are way over to the far right of my table, and were shrunk down so all I saw was "Rep". As I said, I was just closing the database and saw three "Reps", which I thought was weird. Lo and behold, I am going to kill someone tomorrow. Thing is, no one in my group other than me is working on this - I've shown it to a couple of people, but that's it. I am now going to be password protecting everything I have. ARRRRRRRRRGH.

OK, so now that i've wasted 9 full hours, plus all of your time, I need to go eat dinner. I will read the rest of the posts here after that, and will show this thread as resolved.

I'm cursing violently in my head right now.
Chris

Quote:

---

Originally Posted by chris128

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

---

It's nothing to do with the OleDbCommand. It's the underlying OLEDB provider that decides what it supports. In this case I assume that it's for clarity. I always use parameters with names when using Access for clarity, even if they aren't used internally.

OK, so - looks like dminder was the one who guessed that it was a column name problem (ie. that I'm a dolt and should a) password protect my dev db's and b) verify the data occasionally). So, a couple of other points:

- I learnt everything I know about databases using Access, so I guess I have a bit of a nostalgic tie to it. however, now that i've been using SQL Server (although it's still 2000), I have found a new love in Databases. Only reason why I'm using Access at all is because we need to run this application locally and / or off the server, and we have another application that keeps us from using an MSDE.
- There are obviously 14,000 different ways of doing something as simple (ha ha) as returning data from a database; as I'm currently working on performance optimisation for my group's main application, I wonder if there are performance issues depending on what is being queried / returned...things to think about.
- I'd really like to thank chris128, backwoodscoder, dminder, and jmcilhinney (for getting me started). You all definitely rock and your time is appreciated.

OK, I'm going back to coding. This day will be one I laugh about, but maybe tomorrow.

Thanks again all!!
Chris

haha thanks, im just glad you got it sorted in the end! :)

glad to see you got this one fixed. You should carry a yardstick and smack peoples hands when they touch your work without asking :P