tekgod01

This is all I get: An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll Additional information: System error.

Sadly, I don't get to mess with Visual Basic very much and so my brain likes to rust. So when I have to come back to it about once a year, I have to relearn everything. Yay. I'm trying to get a listbox to populate with some SQL data. When I run it, it always stops at "myConnection.Open" with an error. Here's what I got so far. Am I missing something? Public Class fmMain Inherits System.Windows.Forms.Form Dim myConnection As SqlConnection = New SqlConnection("server=home;database=dbRawMaterials") Dim myDataAdapter As New SqlDataAdapter Dim myDataSet = New DataSet --- Private Sub fmMain_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load myDataAdapter.SelectCommand = New SqlCommand myDataAdapter.SelectCommand.Connection = myConnection myDataAdapter.SelectCommand.CommandText = "SELECT PartNumber FROM tblRawMaterials" myDataAdapter.SelectCommand.CommandType = CommandType.Text myConnection.Open() myDataAdapter.SelectCommand.ExecuteNonQuery() myDataAdapter.Fill(myDataSet, "PartNumber") myConnection.Close() lstParts.DataSource = myDataSet End Sub --- Any help would be appreciated. Thank you.

Thank you both. It took some time to figure out what either of you were saying (I'm still pretty new to this - this is my third program). I finally figured out krinpit's post but you forgot to mention that I needed to make the control on Form1 public. Again, thank you both.

Don't you mean "Bleeding edge?" Every time a new "must-have" game comes out, I'm selling blood so I can get the latest video card/memory upgrade/hard drive/cpu just so I can play it. Thief III rules Deus Ex wasn't worth it HalfLife2 should be worth it Halo2 better be worth it Doom3 - As long as they upgrade the graphics, they can keep the mission style the same and it would be worth it. My vote is for Doom3 - id/Activision would have to go out of their way to screw it up.

Form1 has Label1, Label2, Label3, Label4, etc. When the user clicks on one of the labels, it calls up Form2, which has Labelx, Textbox1, and Button1. If Label1 was clicked, Form2 will have a title (aka, Form2.Text) "See" If Label2 was clicked, Form2 will have a title "Spot" If Label1 was clicked, Labelx will have the text "Enter See" If Label2 was clicked, Labelx will have the text "Spot Run" When the user clicks on Button1, the text entered into Textbox1 will fill in the text of the label that was clicked. I can change the text for Form2, no problem. I can change Labelx, no problem. I just can't bring the text back to Form1 to fill in Label1. Any suggestions? [edit] Almost forgot. Form1 is never hidden or anything. Form2 just pops up in front of Form1 and then closes when the user clicks on Button1. Form1 never moves. [/edit]

Double-click on the error line at the bottom of the screen. A new window will pop-up asking you which form the name is supposed to be.

Thank you.

*sigh* Case 7 : lblSerial.Text = "G" & Now.ToString("ydd")

Our company uses a serial number that is based on today's date. The month has a letter equivalent, then the last digit of the year, then the day. I'm using Case for the month and adding Today.Day for the day. How do I add the last digit of the year? For example, today's serial number would be: G426 I'm stuck on trying to get the four out of the year. Thank you.

Isn't there some other way to do this without the need of all the SQL statements?

For future posterity, here's what I have that updates the database using an SQL statement. It's not pretty ("flawed", by some accounts), but it works, dernit... Dim PartDataReader As SqlClient.SqlDataReader Dim cmd As New SqlClient.SqlCommand("Update SideLabels SET Quantity = '" & CType(txtQuantity.Text, String) & "', Weight = '" & CType(txtWeight.Text, String) & "' Where PartNumber = '" & CType(txtPartNumber.Text, String) & "'", SqlConnection1) SqlConnection1.Open() PartDataReader = cmd.ExecuteReader SqlConnection1.Close() I still have to put in the remaining seven textboxes, but it works. Of course, I'd still like to know a better way of doing this. My next program will require a lot more than 10 textboxes. Preliminary estimates put it closer to 100 and I just don't wanna have 1 line that friggin' long.

Holy crap. It worked. At least, it looks like it worked.

Certainly. Getting this solved is pretty important. I have a deployment on hold until I can get this figured out. Would using an SQL statement do a proper update? If so, what VB code would I use? I have a partial statement already written but I'm unsure as to how it would get executed. Dim cmd As New SqlClient.SqlCommand("Update SideLabels SET Quantity = '" & CType(txtQuantity.Text, String) & "' Where PartNumber = '" & CType(txtPartNumber.Text, String) & "'", SqlConnection1)

That was last year. Sorry. It's been put into the great recycle bin of the sky. It took me all of about 3 days to do it, though. If you help me with this problem, I'll rewrite and send it to you. Are you bribable? :D

Ok, I've taken out the searchPart line (I noticed it wasn't used for anything after I had posted the code). You didn't say anything about CurrentRow so I assume that this line is ok (along with the "With..." statements) and I left them alone. Here's my dim expressions as of now: dim CurrentRow as DataRow dim myCurrencyManager as CurrencyManager = CType(Me.BindingContext(DataSet1), CurrencyManager) I know that I have to do something with the currency manager, but Microsoft's help doesn't actually help. Go figure. ;) It looks like I need some real help in this area. My SqlDataAdapter1 and DataSet1 were set up using the wizard within VB. Towards the end of the code, I have ".AcceptChanges". Should I change this to Update? I know. It's my first real program (other than Yahtzee for class). I expected my code to be ugly as I'm learning all this as I'm coding it. I also have a couple of Wrox books but they're a bit confusing if you don't start at the very beginning. I want to learn what they're talking about in Chapter 11. In order to understand it, I have to go through Chapters 1 to 10 since they keep building on the same code. Unfortunately, I don't have that kind of time.

I have a program. No, really, I do! The user inputs a part number into a textbox. The program goes into an SQL database and returns the other rows (based on the part number) and fills in textboxes and radio buttons. No problem. The user can also put in a new part number. When finished, the user clicks on a button and the new part number is inserted into the database. No problem. The user can also edit a part number. Any information (other than the part number) can be changed. The user clicks on a button and the part number is edited. Problem. Every time (yes, every time) the user tries to edit a part number, it deletes the first row of the database and then tries to insert the edited part number as a new part number. Here's my error: An unhandled exception of type 'System.Data.ConstraintException' occurred in system.data.dll Additional information: Column 'PartNumber' is constrained to be unique. Value '10101' is already present. I've looked and tried changing all kinds of settings and code lines but the error never changes (other than the Value '10101' <--is part number). Here's the code behind the curtain. Private Sub btnAddChange_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddChange.Click 'What the program does if the part information is new If btnAddChange.Text = "Add New Part" Then 'Creates new row Dim NewRow As DataRow NewRow = DataSet1.Tables("SideLabels").NewRow With NewRow 'Adds the information to the new row .Item("PartNumber") = Me.txtPartNumber.Text .Item("Quantity") = Me.txtQuantity.Text .Item("Weight") = Me.txtWeight.Text .Item("ULCert") = Me.lblULRU.Text .Item("CSACert") = Me.lblCSA.Text .Item("Supplier") = Me.txtSupplier.Text .Item("Revision") = Me.txtRevision.Text .Item("Description") = Me.txtDescription.Text .Item("WireGauge") = Me.txtWireGauge.Text .Item("HAPNumber") = Me.txtHAPNumber.Text End With 'Sends the new row to the SQL server DataSet1.Tables("SideLabels").Rows.Add(NewRow) 'Updates the program's database to include the new row SqlDataAdapter1.Update(DataSet1) End If 'What the program does if the part information is existing If btnAddChange.Text = "Accept Changes" Then 'Finds row that is being updated Dim searchPart As String = txtPartNumber.Text Dim CurrentRow As DataRow CurrentRow = DataSet1.Tables("SideLabels").Rows(Me.BindingContext(Me.DataSet1.Tables("SideLabels")).Position) With CurrentRow 'Changes the information to the row .BeginEdit() .Item("PartNumber") = Me.txtPartNumber.Text .Item("Quantity") = Me.txtQuantity.Text .Item("Weight") = Me.txtWeight.Text .Item("ULCert") = Me.lblULRU.Text .Item("CSACert") = Me.lblCSA.Text .Item("Supplier") = Me.txtSupplier.Text .Item("Revision") = Me.txtRevision.Text .Item("Description") = Me.txtDescription.Text .Item("WireGauge") = Me.txtWireGauge.Text .Item("HAPNumber") = Me.txtHAPNumber.Text .EndEdit() End With 'Updates the SQL server with the changed information DataSet1.Tables("SideLabels").AcceptChanges() 'Updates the program to include the changed information SqlDataAdapter1.Update(DataSet1.SideLabels) End If 'Clears all the textboxes (see txtPartNumber_LostFocus) txtPartNumber.Text = "" txtPartNumber.Focus() btnExit.Focus() SqlDataAdapter1.Update(DataSet1.SideLabels) txtPartNumber.Focus() End Sub Obviously, I'm missing something or the code is written wrong. Please - I'm begging you - help me! Thank you.

The word "idiot" occurs quite frequently in my head. Once again, I found the solution. For others' sake, I will post the solution: Dim PartDataAdapter As New SqlClient.SqlDataAdapter _ ("Select * From SideLabels Where PartNumber = '" & CType(searchPart, String) & "'", SqlConnection1) If you notice, I added the ' after the equals sign and then added & "'" after the ctype. It's funny how I can find the answer AFTER I posted the question...

I have a database (BoxLabels) with one Table (SideLabels). When I enter in a part number, it looks it up in the table and comes back with the rest of the information for that part. I have no problems with this. I also have no problems if the search nets no results. The problem that I have is when the search uses text. For example, part number 15000 comes back ok. Part number AA causes the program to crash. I'm using SQL as the database and here's the table breakdown: Column Name Data Type Length Allow Nulls PartNumber varchar 50 No (Primary Key) Quantity numeric 9 No Weight numeric 9 No ULCert varchar 50 No CSACert varchar 50 No Supplier varchar 50 No Revision varchar 50 No Description varchar 50 No WireGauge varchar 50 No HAPNumber varchar 50 No What else...Ah, yes - the code. Here is the entire length of code for the textbox that the user will enter the part number in: Private Sub txtPartNumber_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles txtPartNumber.LostFocus 'Ignores everything if blank. Used if user needs to close without putting information in. If txtPartNumber.Text = "" Then txtQuantity.Text = "" txtWeight.Text = "" txtSupplier.Text = "" txtDescription.Text = "" txtWireGauge.Text = "" txtHAPNumber.Text = "" txtRevision.Text = "" btnAddChange.Text = "" btnAddChange.Visible = False rdoULNo.Checked = True rdoCSANo.Checked = True Exit Sub End If Dim searchPart As String = txtPartNumber.Text Dim PartDataAdapter As New SqlClient.SqlDataAdapter _ ("Select * From SideLabels Where PartNumber = " & CType(searchPart, String), SqlConnection1) Dim Command As New SqlClient.SqlCommandBuilder(PartDataAdapter) Dim PartDataSet As New DataSet Dim objRow As DataRow Dim cmd As New SqlClient.SqlCommand _ ("Select * From SideLabels Where PartNumber = " & CType(searchPart, String), SqlConnection1) Dim PartDataReader As SqlClient.SqlDataReader SqlConnection1.Open() PartDataReader = cmd.ExecuteReader If PartDataReader.Read = False Then 'do function for when there are no rows SqlConnection1.Close() btnAddChange.Visible = True btnAddChange.Text = "Add New Part" txtQuantity.Text = "" txtWeight.Text = "" txtSupplier.Text = "A/TF-B" txtDescription.Text = "" txtWireGauge.Text = "" txtHAPNumber.Text = "" txtRevision.Text = "" rdoULNo.Checked = True rdoCSANo.Checked = True Exit Sub Else 'do function for when there are rows btnAddChange.Visible = True btnAddChange.Text = "Accept Changes" End If SqlConnection1.Close() PartDataAdapter.FillSchema(PartDataSet, SchemaType.Source, "SideLabels") PartDataSet.Clear() PartDataAdapter.Fill(PartDataSet, "SideLabels") objRow = PartDataSet.Tables("SideLabels").Rows.Find(searchPart) txtQuantity.Text = objRow.Item("Quantity") txtWeight.Text = objRow.Item("Weight") txtSupplier.Text = objRow.Item("Supplier") txtDescription.Text = objRow.Item("Description") txtWireGauge.Text = objRow.Item("WireGauge") txtHAPNumber.Text = objRow.Item("HAPNumber") txtRevision.Text = objRow.Item("Revision") lblULRU.Text = objRow.Item("ULCert") If lblULRU.Text = "UL.PCX" Then rdoUL.Checked = True ElseIf lblULRU.Text = "RU.PCX" Then rdoRU.Checked = True End If lblCSA.Text = objRow.Item("CSACert") If lblCSA.Text = "CSA.PCX" Then rdoCSAYes.Checked = True End If End Sub The program will crash whether the search is text or if a row in the database is text. If text is involved in any way, I'm screwed. Any ideas?

Found the answer: Private Sub txtPartNumber_LostFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtPartNumber.LostFocus If txtPartNumber.Text = "" Then MessageBox.Show("You must enter in a part number!", "Missing Information", MessageBoxButtons.OK, MessageBoxIcon.Exclamation) txtPartNumber.Focus() Exit Sub End If SqlConnection1.Open() Dim cmd As New SqlClient.SqlCommand("Select * From PartInformation Where PartNumber = " & CType(searchPart, String), SqlConnection1) Dim dr As SqlClient.SqlDataReader dr = cmd.ExecuteReader If dr.Read = False Then 'do function for when there are no rows SqlConnection1.Close() Exit Sub Else 'do function for when there are rows End If SqlConnection1.Close()

Of course, solving one problem just creates a new problem. I got rid of the button and now have it look up the part number when the textbox loses focus. What can I do to have a messagebox pop up when the search returns no results? I know the messagebox code, just not the no-value-returned code stuff. Thank you.

I found the answer! I must say that I love this site. The only catch is that you need to find the right keywords to find the answer you're looking for. Anyway, here's my solution (adapted from http://www.xtremedotnettalk.com/showthread.php?t=49521&highlight=database+search): Private Sub btnLookUpPart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLookUpPart.Click Dim searchPart As String = txtPartNumber.Text Dim DataAdapter1 As New SqlClient.SqlDataAdapter("Select * From PartInformation Where PartNumber = " & CType(searchPart, String), SqlConnection1) Dim Command As New SqlClient.SqlCommandBuilder(DataAdapter1) Dim DataSet99 As New DataSet DataAdapter1.FillSchema(DataSet99, SchemaType.Source, "PartInformation") DataSet99.Clear() DataAdapter1.Fill(DataSet99, "PartInformation") Dim objRow As DataRow objRow = DataSet99.Tables("PartInformation").Rows.Find(searchPart) txtQuantity.Text = objRow.Item("Quantity") End Sub

I have a textbox that a user will enter in a part number. The user will then click a button to look that part number up in a database. The next textbox will then have the resulting information from the lookup. However, when the button is clicked, the result is always the first line. What am I missing? This is the code behind my pretty button... Private Sub btnLookUpPart_Click(ByVal sender As System.Object, ByVal e _ As System.EventArgs) Handles btnLookUpPart.Click Dim CurrentRow As DataRow CurrentRow = PartDataSet1.Tables("PartInformation").Rows _ (Me.BindingContext(Me.PartDataSet1.Tables("PartInformation")).Position) With CurrentRow .Item("PartNumber") = Me.txtPartNumber.Text Me.txtQuantity.Text = .Item("Quantity") End With End Sub Thank you very much for any help you can provide.