I am a relative beginner at VB.NET so my methods may be a little off. I am trying to pull the data from 2 fields from 1 table and creating a new record with name and employee id. I thought it would be a relatively simple process to capture an array with the name and ID, then use the array in the SQL insert variables to create each record. Thank you for responding,
LWhite
I am trying to create an order form for my users. The form has a listbox where the user selects multiple names and it populates a textbox for user confirmation. The selection process also creates an array used to create a SQL database record for each name in the list. This part works. Here is the code I have so far:
Sub xxx
reader1 = dr1.ExecuteReader()
While reader1.Read()
values1.Add(reader1.Item("Name"))
values2.Add(reader1.Item("Emplid"))
End While
lbName.DataSource = values1
lbEmplid.DataSource = values2
End Sub
Sub xxx
rtbName.Text = Nothing
rtbEmplid.Text = Nothing
For i = 0 To lbName.SelectedItems.Count - 1
With arrList2
.ToArray()
End With
rtbName.Text = rtbName.Text & lbName.SelectedItems(i) & vbCrLf
Next
i = Nothing
The problem I'm running into is, I need to collect the Emplid along with the name. I'm trying to set it up by selecting only the name with the Emplid selected at the same time. I can combine the two within the SQL statement, but I need to populate each field of the database entry separately. Any help would be greatly appreciated.:) Thank you,
LWhite
Thank you for your suggestions. I rearranged most of my variables to be local. I have another issue you may be able to help me on. I'm using the following code to perform a SQL SELECT to combine the first & last name, and the employee ID number to be used in a listbox. The user selects multiple names that are transfered to a textbox. When they are selected, the array is created, which is used to create a record in a SQL table (one entry per name). The problem I have is, I need to update the Employee ID field but haven't found the right code to extract the "emplid" data. The name field works fine.
Dim sql1 As String = "Select (LastName + ', ' + FirstName) As Name, emplid FROM Users ORDER BY LastName"
Dim strCommandText As String = "INSERT INTO Orders(OrderDate, DeliverDate, DeliverLocation, Name, Special, Comments, Rate, EMPLID, LoggedIP)" & "VALUES('" & strOrderDate & "','" & strDeliverDate & "','" & strDeliverLocation & "','" & strName & "','" & strSpecial & "'" & ",'" & strComments & "','" & strRate & "','" & strEMPLID & "','" & strLoggedHost & "')"
Private Sub lbName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbName.SelectedIndexChanged
Dim i As Integer
editName.Text = Nothing
For i = 0 To lbName.SelectedItems.Count - 1
arrList2.Add(lbName.SelectedItems(i))
With arrList2
.ToArray()
End With
editName.Text = editName.Text & lbName.SelectedItems(i) & vbCrLf
Next
i = Nothing
End Sub
Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim i As Integer
Dim r As String
Dim x As String
editComments.Clear()
strComments = editComments.Text
Try
SqlConnection1.Close()
cnn1.Close()
SqlConnection1.Open()
' Create a name array from editName textbox; Used to create a new record for each name
For i = 0 To arrList2.Count - 1
strName = arrList2.Item(i)
x = arrList2.Item(i)
Dim strCommandText As String = "INSERT INTO Orders(OrderDate, DeliverDate, DeliverLocation, Name, Special, Comments, Rate, EMPLID, LoggedIP)" & "VALUES('" & strOrderDate & "','" & strDeliverDate & "','" & strDeliverLocation & "','" & strName & "','" & strSpecial & "'" & ",'" & strComments & "','" & strRate & "','" & strEMPLID & "','" & strLogge
When the form opens, I have a "Dim strName As String" along with other variables. Thank you, you got me thinking and I rearranged my code. It works now. Here is what it looks like now:
Private Sub lbName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbName.SelectedIndexChanged
editName.Text = Nothing
tbEmplid.Text = Nothing
For i = 0 To lbName.SelectedItems.Count - 1
strName = lbName.SelectedItems(i) & vbCrLf '.ToString & vbCrLf
arrList2.Add(strName)
With arrList2
.ToArray()
End With
editName.Text = editName.Text & lbName.SelectedItems(i) & vbCrLf
Next
End Sub
Note: I moved the "strName = lbName.SelectedItems(i) & vbCrLf" line above the "arrList2.Add(strName)" line, instead of being the last line. It sequences through nicely now. I trash the array, is it is only for the record creation and not needed after that. Thank you again! I hope posting this code will help someone else!:cool:
LWhite
Here are the other relevant parts of my code. I build the textbox entries with the following, which also drops the selected names into the array:
Private Sub lbName_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbName.SelectedIndexChanged
editName.Text = Nothing
For i = 0 To lbName.SelectedItems.Count - 1
arrList2.Add(strName)
With arrList2
.ToArray()
End With
editName.Text = editName.Text & lbName.SelectedItems(i) & vbCrLf
strName = lbName.SelectedItems(i) & vbCrLf
Next
End Sub
Here is my complete code that is supposed to create a new database record with each iteration of the name array:
For i = 0 To arrList2.Count - 1
strName = arrList2.Item(i)
x = arrList2.Item(i)
Dim strCommandText As String = "INSERT INTO Orders(OrderDate, DeliverDate, DeliverLocation, Name, Special, Comments, Rate, EMPLID, LoggedIP)" _
& "VALUES('" & strOrderDate & "','" & strDeliverDate & "','" & strDeliverLocation & "','" & strName & "','" & strSpecial & "','" & strComments & "','" & strRate & "','" & strEMPLID & "','" & strLoggedHost & "')"
Dim cmd As New SqlClient.SqlCommand(strCommandText, SqlConnection1)
cmd.ExecuteNonQuery()
Next
Thank you for your assistance.:)
LWhite
I adjusted my code as follows:
Dim x As String
For i = 0 To arrList2.Count - 1
x = arrList2.Item(i)
strName = arrList2.Item(i)
I now get the qty of data entries in my database equal to the number of names I select in the textbox, but the last entry is blank. If I select 3 names, I get the first 2 database entries with the names, but the last one is blank (name field). It appears the first iteration leaves strName blank. Thanks again for the assistance.
LWhite
Thank you. The tags do make it alot easier to read.
LWhite
I am having difficulty iterating through an array. I have found the following sample code and tried modifying it with my variables. ' Sample Array Read Code 'For i = 0 To arrList2.Count - 1 ' Console.WriteLine(CStr(arrList2.Item(i))) 'Next After modifying line 3 with x = CStr(arrList2.Item(i)) , I receive the following error: "Value of type 'String' cannot be converted to '1-dimensional array of String'." X is defined as: Dim x() As String I need to get each element (which is a name read from a textbox) into a variable strName. This is to allow my SQL statement to create a record for each name in the array. Any help would be greatly appreciated! Thank you in advance.:)
LWhite
Thank you, I replaced the LIKE with = as I didn't need a wildcard search anyway. I will have to look at tightening the code to protect from attack. I stepped through the code and find that if I leave the SShop1 variable = nothing, I get my entire database, as expected, but when I try to set the variable to = a selection from the listbox, I get nothing displayed, just an empty datagrid. This is the line I rem'd out to get a default variable of nothing: SShop1 = lbShop.SelectedItem Thank you for responding to my question. Larry
LWhite
:confused: Any help would be appreciated. I am trying to design my form to display the data using a Selected Value from a listbox. I set the lbShop.SelectedIndexChanged event to refresh the datagrid when a new value is selected. The variable SShop1 is changed correctly as each item in the listbox is selected, but the datagrid will not refresh using the SQL Select string. What I need is the new SQL Select command to run with the user selected variable, clear the datagrid, and display the data from the new SQL string. Private Sub frmAllOpenbyShop_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load cnn.Open() reader = dr5.ExecuteReader() While reader.Read() values.Add(reader.Item("Shops").ToString()) End While lbShop.DataSource = values reader.Close() Try 'Attempt to load the dataset. Me.loadDoc() Catch eLoad As System.Exception 'Add your error handling code here. 'Display error message, if any. System.Windows.Forms.MessageBox.Show(eLoad.Message) End Try End Sub ******************************************************************************* Public Sub dadapt1() SShop1 = lbShop.SelectedItem DAdapt.SelectCommand = New SqlCommand DAdapt.SelectCommand.CommandType = CommandType.Text DAdapt.SelectCommand.Connection = cnn1 DAdapt.SelectCommand.CommandText = "Select SystemName, Shop, ActionTaken, TaskNotes, Symptoms, TroubleShooting, Conclusion FROM Workorders WHERE TaskNotes LIKE '" & SShop1 & "' ORDER BY Shop" End Sub ******************************************************************************* Public Sub loadDoc() Me.dadapt1() Try dsSearch2.Clear() DAdapt.Fill(dsSearch2) Catch ex As SqlException End Try grdWorkorders.DataSource = dsSearch2 End Sub ******************************************************************************* Private Sub lbShop_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lbShop.SelectedIndexChanged grdWorkorders.Refresh() Me.loadDoc() End Sub LWhite
I added another variation to this code that works very nicely to provide a search based on part of the "Keywords" field. Add this to the btnLoad_Click sub: tbSearch.Text = "%" & tbSearch.Text & "%" Again, Thanks to Kevin for the assist! Got the creative juices flowing again, lol.
LWhite
Sweet! Thank you very much, that worked. I appreciate the assistance.:cool:
LWhite
I am trying to populate a datagrid using a SQL select statement. The statement contains a "WHERE fieldname LIKE textbox.text" as below in my code. The problem is, I can't get the code right to display the data in the grid when the button is clicked. Here is my code so far: Public Sub loadDoc() Dim DAdapt As SqlDataAdapter Dim custMap As DataTableMapping = DAdapt.TableMappings.Add("Table", "DocumentList") custMap.ColumnMappings.Add("Title", "Title") custMap.ColumnMappings.Add("Expiration", "Expiration") custMap.ColumnMappings.Add("Keywords", "Keywords") Dim sql2 As String = "Select * FROM DocumentList WHERE Keywords LIKE '" & tbSearch.Text & "'" Dim ds As New DataSet DAdapt = New SqlDataAdapter(sql2, cnn1) Me.cnn1.Open() DAdapt.Fill(ds) Try grdDocumentList.DataSource = ds.DefaultViewManager ds.Clear() ds.Merge(ds) grdDocumentList.SetDataBinding(ds, "DocumentList") Catch eLoadMerge As System.Exception Throw eLoadMerge End Try Me.cnn1.Close() End Sub Thank you, :-D
LWhite
I'm just trying to add a listbox to a datagrid to allow the user to change selected fields. It should be easier than this, lol. I'm getting an error "Object reference not set to an instance of an object", when I select an actual value from my listbox within my datagrid. My code is as follows: Dim myDG As New DataGrid ... Private Sub listControl_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles listControl.SelectedValueChanged myDG(hitTestGrid.Row, hitTestGrid.Column) = listControl.ToString End Sub ... If someone could point me to a good tutorial on editing data in datagrids, I'd appreciate it. Thanks, Larry:)
LWhite
I am having difficulty getting the underlying SQL data to update when a change is made to the datagrid. The change is made using a combobox, and allows the proper selection of 1 of the 3 choices, but I can't seem to get the syntax/code right to update the table. Here is my current code: Public Sub comboControl_SelectedValueChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles comboControl.SelectedValueChanged grdVW_AllOpen(hitTestGrid.Row, hitTestGrid.Column) = comboControl.Text Dim sc1 As New SqlCommand Dim sCnnStr As String sCnnStr = "Data Source=" & SQL_SERVER & ";Initial Catalog=" & DATABASE & ";User id=" & USER_ID & ";Password=" & PWD Dim sqlConn As New SqlConnection(sCnnStr) sc1.Connection = sqlConn sc1.CommandType = CommandType.Text sc1.CommandText = "UPDATE dbo.Chits_Backup SET Status = '" & comboControl.Text & "'" & ", LastName = @LastName, FirstName = @FirstName, DateofRequest = @DateofRequest, FromDate = @FromDate, ToDate = @ToDate, Days = @Days, NatureofRequest = @NatureofRequest WHERE ID = @ID" Dim cmd As New SqlClient.SqlCommand(sc1.CommandText) 'Create the sql command object and set its command type to execute the sql query to get the results sc1.Parameters.Add("@Status", SqlDbType.NVarChar, 50, "Status").Value = comboControl.Text sc1.Parameters.Add("@LastName", SqlDbType.NVarChar, 50, "LastName").Value = "@LastName" sc1.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50, "FirstName").Value = "@FirstName" sc1.Parameters.Add("@DateofRequest", SqlDbType.NVarChar, 50, "DateofRequest").Value = "@DateofRequest" sc1.Parameters.Add("@FromDate", SqlDbType.DateTime, 8, "FromDate").Value = "@FromDate" sc1.Parameters.Add("@ToDate", SqlDbType.DateTime, 8, "ToDate").Value = "@ToDate" sc1.Parameters.Add("@Days", SqlDbType.NVarChar, 50, "Days").Value = "@Days" sc1.Parameters.Add("@NatureofRequest", SqlDbType.NVarChar, 50, "NatureofRequest").Value = "@NatureofRequest" Dim myParm2 As SqlParameter = sc1.Parameters.Add("@ID", SqlDbType.Int, 4, "ID") myParm2.SourceVersion = DataRowVersion.Original sqlConn.Open() Try sc1.ExecuteNonQuery() Catch ex As SqlException MsgBox(ex.Message().ToString()) End Try sqlConn.Close() I know it has to do with the handling of variables, but can't narrow it down. Thank you, LWhite:)
Thank you very much for responding. I plugged in the code and modified to match my database settings and have a syntax error. I should be able to find the error with a little troubleshooting. The error appears to be in the .CommandText line. Here is my modified code: Private Function isEmpExist(ByVal EmpNo As Integer) As Boolean Dim CN As New SqlConnection Dim R As Integer CN = (New SqlConnection("Integrated Security=False; User id= testuser; password= xxx; Server=xxx; Initial Catalog=Galley")) Dim Cmd As SqlCommand = CN.CreateCommand With Cmd 'set commandtype as commandtext .CommandText = "SELECT emplid FROM users WHERE emplid = " & editEMPLID.ToString.Trim End With CN.Open() R = Cmd.ExecuteScalar If IsNothing(R) Then Return False Else Return True End If CN.Close() End Function Again, I very much appreciate the assist. I've been banging my head against the wall for a few days on this one. This should be a simple comparison and validation.:~ LWhite
I am using VB.NET (2003) to create a Windows form to interface with a SQL 2000 database. I need to validate the user input of an employee number with the table that contains the valid numbers. All I want to do is validate the information and move on, or pop a messagebox up. My problem is getting the code right to loop the array for the comparison function. I also hope to match the USERID and integrate this into the validation process to further verify that the proper person is being identified. Thank you in advance. :o LW:-D LWhite
Here is an easy way to capture the hostname for a Windows form in VB.NET Add this entry to declarations: Imports System.Net Establish the variable with the GetHostName function: Dim strLoggedHost = Dns.GetHostName() I searched all over and found many other ways that required more lines of code. This is simple and to the point. :-D LWhite
I thought about just emailing form1, but it has a lot more detail than I need to mail. The second form is streamlined down to only a few data elements. I may have to simplify it even more. Thank you, LWhite
I know this is probably a basic question, but here goes. I am building a Windows form based application. I have a form1 that opens up, users enter information, then I would like to e-mail the selected information from some selected textbox's using another form2. Or is it easier to email the data from the textboxs without a form? A second minor problem is how do you eliminate the Microsoft Outlook message that pops up when activating the Outlook application from within VB.NET? Thank you,:confused: LWhite