|
For my sins I've used VB6 a bit more recently than @Dave-Kreskowiak so I can confirm that he is correct. That functionality is not available.
If you have some fixed values that you want to enter into the list-box it's usual to use the Form_Load event to do that
|
|
|
|
|
Hello everybody!
I am facing two simple problems in the following code:
Dim cmdText As String
cmdText = "SELECT tblStaffInfo.StaffName, tblCampuses.CampusName,
LessonPlanning, LessonPreprn, Regularity,
((LessonPlanning + LessonPreprn + Regularity)/3 ) AS AvgScore
FROM tblStaffInfo
INNER JOIN tblCampuses
ON tblStaffInfo.CampusID = tblCampuses.CampusID
INNER JOIN tblStaffEvaluation
ON tblStaffEvaluation.StaffID = tblStaffInfo.StaffID
ORDER BY tblStaffInfo.StaffName"
(1)- The above Sql shows hatred for the division (/) sign
(2)- How can use short alias for the long tables' name; like tblStaffInfo and tblCampuses etc.
Please suggest!
|
|
|
|
|
To answer your second question first ...
You can give each table a short alias by just including that alias after the table name in the query, or by using AS aliasname. For example you can use either
FROM tblStaffInfo AS A
INNER JOIN tblCampuses AS B ON A.CampusID = B.CampusID
INNER JOIN tblStaffEvaluation AS C ON C.StaffID = A.StaffID or
FROM tblStaffInfo A
INNER JOIN tblCampuses B ON A.CampusID = B.CampusID
INNER JOIN tblStaffEvaluation C ON C.StaffID = A.StaffID
Once you have given a table an alias you can then no longer use the tablename to qualify which fields you want - you must use the alias. For example, this query
SELECT A.StaffName, tblCampuses.CampusName,
LessonPlanning, LessonPreprn, Regularity,
((LessonPlanning + LessonPreprn + Regularity) / 3) AS AvgScore
FROM tblStaffInfo A
INNER JOIN tblCampuses B ON A.CampusID = B.CampusID
INNER JOIN tblStaffEvaluation C ON C.StaffID = A.StaffID
ORDER BY A.StaffName will generate the errorQuote: Msg 4104, Level 16, State 1, Line 24
The multi-part identifier "tblCampuses.CampusName" could not be bound.
It should be B.CampusName.
For the first part of your question you will have to explain to us what "The above Sql shows hatred for the division (/) sign" means. There is no error generated by your query and if the fields are defined as INT on the table schema then it gives the correct results. You will get strange results if you have stored those scores as char or varchar fields.
|
|
|
|
|
(1) For the average problem, are any of these columns nullable? If so, your statement needs to be a bit more complex because NULL is not the same as 0. You might want to try this[^].
(2) As for the alias, it's ridiculously simple:
SELECT si.StaffName, ci.CampusName, ...
FROM tblStaffInfo AS si
INNER JOIN tblCampuses AS ci
ON si.CampusID = ci.CampusID
...
|
|
|
|
|
Thank you very much CHill60 and Dave for your help, especially very beautiful explanation by CHill60.
Now, my problem got resolved.
Wish you very good time and regards.
|
|
|
|
|
Hello all,
I have one question regarding migrating excel to datagridview.I have an excel consists of 4 sheets can this be acheived in datagridview or any other tool in dotnet,if so please reply me how can we acheive this.
Thanks & Regards,
Sampath
|
|
|
|
|
Yes it can be achieved, but you need to provide more details of what you are trying to do. You can display a single sheet in a DataGridView directly with binding. If you wish to display the content of all four sheets then you would need some code to read and rearrange all the data.
|
|
|
|
|
Thanks for you reply.
I have an excel contains 4 sheets which has data,i want to display 4 sheets data in four different datagridviews(Do datagridview has concept of tabs ?).Kindly tell the best way to display the data
|
|
|
|
|
Thanks for you reply.
I have an excel contains 4 sheets which has data,i want to display 4 sheets data in four different datagridviews(Do datagridview has concept of tabs ?).Kindly tell the best way to display the data.
|
|
|
|
|
|
Hello everybody!
A newer problem on a newer day 
This time, I am facing a very basic issue in the following code:
Private Function GetDataSource(strCampusName) As DataTable
Dim cmdText As String = "SELECT * from tblStaffInfo tbl WHERE tbl.CampusName = " + strCampusName
Dim conxnString As String = "Data Source=Raabi\SQLEXPRESS;
Initial Catalog=StaffReport; Integrated Security=True"
Dim DGVadapter As New SqlDataAdapter()
Dim ds As New DataSet()
Dim conxn As New SqlConnection(conxnString)
Dim cmd As SqlCommand = conxn.CreateCommand()
Try
cmd.CommandText = cmdText
DGVadapter.SelectCommand = cmd
conxn.Open()
DGVadapter.Fill(ds)
Return ds.Tables(0)
conxn.Close()
Catch ex As Exception
MsgBox("Error: " & ex.Message)
End Try
End Function
Error:
Invalid column name 'whatever'
Even if I use, for example;
Dim cmdText As String = "SELECT * from tblStaffInfo tbl WHERE tbl.CampusName = CityCampus"
I receive the same error.
Any help, please!
|
|
|
|
|
Sorry everybody, I was committing a syntax error. It must be:
Dim cmdText As String = "SELECT * from tblStaffInfo tbl WHERE tbl.CampusName = '" & strCampusName & "'"
It is resolved.
|
|
|
|
|
|
I have gridview with 4 columns(Request_id,Allocate_id,number_plate,name)
I have to populate data from sql server table in number_plate which is columntype DataGridViewComboBoxColumn.
i have written below code:
Private Sub pop_num_DGV()
Dim conn As New SqlConnection
Dim cmd As New SqlCommand
Dim cbn As DataGridViewComboBoxColumn = DirectCast(dgvtarget.Columns(2), DataGridViewComboBoxColumn)
conn = GetConnect()
conn.Open()
cmd.Connection = conn
Try
cmd.CommandText = "select distinct number_plate from tbVehicle where status='Available'"
Dim Adpt As New SqlDataAdapter(cmd.CommandText, conn)
Dim ds As New DataSet()
cbn.DataSource = ds.Tables("tbVehicle")
cbn.HeaderText = "Number_Plate"
cbn.DataPropertyName = "Number_Plate"
cbn.DisplayMember = "Number_Plate"
cbn.ValueMember = "Number_Plate"
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
But still data is not populating in gridview combobox.
kindly help this on priority.Its urgent for me.
|
|
|
|
|
Have you used your debugger to check that the SQL query is returning some records? Have you tried using the exact same spelling for your member name as the database column, i.e "number_plate" rather than "Number_Plate"?
|
|
|
|
|
i checked sql query returning value and i tried member name as database column.But still same issue.kindly help on this.I am new this technology.
modified 5-Jun-16 16:45pm.
|
|
|
|
|
|
Hi everybody
I had been trying to code a DatagridView with multiple tables, without much success, until I found the following code; which seems fulfilling my aspirations:
Private Function GetDataSource() As DataTable
Const sqlSelect As String = "SELECT a.Col1 AS aCol1, a.Col2 AS aCol2, b.Col1 AS bCol1, b.Col2 AS bCol2 " & _
"FROM dbo.TableA AS a
INNER JOIN dbo.TableB AS b ON a.IdCol = b.aIdCol " & _
"ORDER BY aCol1 ASC, bCol1 ASC"
Try
Dim table = New DataTable()
Using con = New MySqlConnection(My.Settings.MySqlConnectionString)
con.Open()
Using da = New MySqlDataAdapter(sqlSelect, con)
da.Fill(table)
Return table
End Using
End Using
Catch ex As Exception
Throw
End Try
End Function
me.DGV1.DataSource = GetDataSource()
But, unfortunately, it gives a couple of the following errors:
MySqlConnection is not defined
MySqlDataAdapter is not defined
Looking forward for some explanation and remedy for these errors.
Please help!
|
|
|
|
|
The code and your title have nothing to do with each other.
Declaring a const within a method is just wrong, it should be a class level constant!
Having declared it you do not use it instead you use My.Settings.MySqlConnectionString which does not exist.
MySqlDataAdapter is also not declared in the method (it is probably declared somewhere else in the source you copied from).
You are running across the fundamental problem with cut and paste learning. You are trying to use some code you found with no understanding what it does. You will now get piecemeal explanations which you do not understand (above is an example).
Get a book, READ it and work through the examples, when you have a basic understanding come back and we can be of more use to you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Declaring a const within a method is just wrong, it should be a class level constant!
There's nothing wrong with declaring a Const in a method if it's only used within that method.
For SQL queries, it's even a good idea, because it prevents you from concatenating user input into the query, and forces you to use parameters. 
Mycroft Holmes wrote: Having declared it you do not use it instead you use My.Settings.MySqlConnectionString which does not exist.
You're looking at the wrong line - it's used on the line immediately below that:
Using da = New MySqlDataAdapter(sqlSelect, con)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Constant - I learn something every now and then, makes it worth hanging around.
I also did not know that about the dataadaptor, I explicitly open and close the connection but then I have not looked at the mechanics of DB comms for ages.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
What DBMS are you using? Your query looks like a Microsoft SQL Server query, but you're trying to use the MySQL classes to execute it.
If your database is MS SQL Server, use the classes from the System.Data.SqlClient namespace:
Using con = New SqlConnection(My.Settings.SqlServerConnectionString)
Using da = New SqlDataAdapter(sqlSelect, con)
da.Fill(table)
Return table
End Using
End Using
If your database is MySQL, you'll need to install Connector/Net[^], add a reference to it from your project, and add Imports MySql.Data.MySqlClient at the top of your code file.
NB: The *DataAdapter classes will open and close the connection for you. There's no need to call con.Open() before you call da.Fill(...).
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks a lot for the responses and sorry for the late acknowledgment.
Let me try your suggestions and may get back to you geeks for further help, if necessary.
Actually, I am using Microsoft SQL Server. I have fair knowledge of programming in VB.NET, in general, but not in databases.
Would anyone suggest the necessary correction, in view of using MS SQL Server, because this piece of code can serve my purpose, at the moment.
Have a good time.
modified 24-May-16 22:30pm.
|
|
|
|
|
Sorry for another show up with hope.
I am still stuck with the
Dim conxnString As String = My.MySettings.sqlConnectionString
Or
Dim conxnString As String = My.Settings.sqlConnectionString
VisualStudio 2015 says "sqlConnectionString is not a member of MySettings"
Do I need to Import some Library, other than System.Data.SqlClient ?
My whole code is as below:
Private Function cboCampuses_SelectedValueChanged(sender As Object, e As EventArgs) Handles cboCampuses.SelectedValueChanged
Dim selectedValue As String
selectedValue = cboCampuses.SelectedValue
Me.StaffEvaluationDGV.DataSource = GetDataSource(selectedValue)
End Function
Private Function GetDataSource(selectedValue) As DataTable
Dim sqlSelect As String = "SELECT * FROM tblEvaln " &
"WHERE CampusName = " & selectedValue
Dim SqlConnection As SqlConnection
Dim conxnString As String = My.MySettings.sqlConnectionString
Try
Dim table = New DataTable()
Using con = New System.Data.SqlClient.SqlConnection(conxnString)
con.Open()
Using da = New System.Data.SqlClient.SqlDataAdapter(sqlSelect, con)
da.Fill(table)
Return table
End Using
End Using
Catch ex As Exception
End Try
End Function
Would anybody help me please!
|
|
|
|
|
Use My.Settings.MySqlConnectionString, assuming the value is a valid SQL Server connection string[^].
I just changed the name to highlight the fact that it's not a MySQL connection string. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
