|
The code you've posted isn't touching your DataGridView. You load the data from the database, and then immediately update the database with the data you've just loaded. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks, Richard, for your attention and sorry for leaving out a change in the code, for the test purpose, as below:
Dim strUpdate As String = "UPDATE StaffName SET StaffName = 'Anonymous'" & "WHERE StaffID = 1005"
A record with the StaffID = 1005 exists.
But the record doesn't get changed, as expected (by me  ). What mistake I am doing.
Please suggest.
I have tried the following code, as well:
Private Sub StaffInfoAdapterUpdate()
Dim ConxnString As String = "Data Source=Dar-e-Arqam\SQLEXPRESS; Initial Catalog=StaffReport;
Integrated Security=True"
Dim Conxn As New SqlConnection(ConxnString)
Dim cmd As SqlCommand = Conxn.CreateCommand()
Dim StaffInfoDS As New DataSet()
Dim StaffInfoAdapter As New SqlDataAdapter()
Dim cmdText As String = "SELECT StaffID, StaffName, tblCampuses.CampusName, Designation,
DOB, Gender, NIC, DateOfJoining
FROM tblStaffInfo
INNER JOIN tblCampuses
ON tblStaffInfo.CampusID = tblCampuses.CampusID"
Dim strUpdate As String = "UPDATE StaffName " &
"SET StaffName = 'Anonymous' " & "WHERE StaffID = 1005"
Try
Conxn.Open()
cmd.CommandText = strUpdate
cmd.ExecuteNonQuery()
MsgBox("The record updated. Really?")
Catch ex As Exception
MsgBox("Error: " & ex.Message)
End Try
End Sub
The above code throws an error: "Invalide object name 'StaffName'.
I am very disturbed for this problem.
Please help.
modified 2-Jun-16 23:26pm.
|
|
|
|
|
Your table is called tblStaffInfo, so your update statement needs to be
Raabi Anony wrote: Dim strUpdate As String = "UPDATE tblStaffInfo " &
"SET StaffName = 'Anonymous' " & "WHERE StaffID = 1005"
Try
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
Wow, such a major mistake, I had been overlooking.
Thanks Chris Quinn and keep your glass always FULL
|
|
|
|
|
Hello everybody
My following code seems very non-cooperative and issues the error: Dynamic SQL generation is not supported against multiple base tables
Dim conxnString As String = "Data Source=Raabi\SQLEXPRESS; Initial Catalog=StaffReport; Integrated Security=True"
Dim conxn As New SqlConnection(conxnString)
Dim cmd As SqlCommand = conxn.CreateCommand()
Dim StaffInfoDS As New DataSet()
Dim StaffInfoAdapter As New SqlDataAdapter()
Dim StaffInfoTbl As DataTable
Private Sub PopulateStaffInfoDGV()
Dim cmdText As String
cmdText = "SELECT StaffID, StaffName, tblCampuses.CampusName, Designation, DOB, Gender, NIC, DateOfJoining
FROM tblStaffInfo
INNER JOIN tblCampuses
ON tblStaffInfo.CampusID = tblCampuses.CampusID"
Try
cmd.CommandText = cmdText
StaffInfoAdapter.SelectCommand = cmd
conxn.Open()
StaffInfoAdapter.Fill(StaffInfoDS, "StaffInfoTbl")
StaffInfoDGV.DataSource = StaffInfoTbl
conxn.Close()
Catch ex As Exception
MsgBox("Error: " & ex.Message)
End Try
End Sub
Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
Dim cmdbldr As New SqlCommandBuilder(StaffInfoAdapter)
cmdbldr.GetUpdateCommand()
StaffInfoAdapter.Update(StaffInfoDS, "StaffInfoTbl")
End Sub
Any help is highly appreciated.
|
|
|
|
|
According to SqlCommandBuilder Class (System.Data.SqlClient)[^]
Quote: Automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated SQL Server database. This class cannot be inherited.
The key word here is "single-table".
You have to use the appropriate InsertCommand, UpdateCommand, or DeleteCommand to do the job as shown in Updating Data Sources with DataAdapters[^]
Peter Leow
https://www.amazon.com/author/peterleow
modified 30-May-16 0:24am.
|
|
|
|
|
Thanks, Peter, for the attention.
Let me try the stuff at the link, provided by you. I may get back, if further guidance is needed.
Regards
|
|
|
|
|
As the error states you cannot update the 2 tables via a dataadaptor. The inner join is getting data from 2 table and presenting it to you UI.
You need to only update the tblStaffInfo as the campus name is there for display purposes only. I used to do the following:
get an empty datatable record by passing in Select * from tblStaffInfo where 1=-1
Add a record to the enpty datatable
populate it with the updated content from your joined datatable
pass the new record to the dtataadaptor to do the update.
Clumsy and ugly but it gets the job done. This is a learning process for you and will be invaluable in the future giving you a grounding in database manipulation.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank, RAH, for hinting towards a new direction for solution.
I will try to implement the idea. Unfortunately, I am almost a novice in databases and therefore I have to struggle a lot in order to grasp the hints.
I will appreciate, if you could emphasize a bit more on your point.
Regards
|
|
|
|
|
|
Thanks, Mycroft, for the link. I will try to follow it and may get back, if needed.
Regards
|
|
|
|
|
I want to save some values into multi columns of a list-box in classic visual basic (vb v.:1.0~6.0) that can use it when we added the component of "Microsoft Form 2.0 Library" to our toolbar, but only can Add items run-time into it! (just programmatically!) Please help.
|
|
|
|
|
As far as I can remember, and I haven't used VB6 in 15 years!, what you want to do is not supported.
|
|
|
|
|
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.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
