|
Imports System.Data.OleDb
..
Dim provider As String
Dim dataFile As String
Dim connString As String
Dim myConnection As OleDbConnection = New OleDbConnection
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
provider = Microsoft.ACE.OLEDB.4.0;Data Source = "
dataFile = "C:\DBtest\test.mdb"
connString = provider + dataFile
myConnection.ConnectionString = connString
myConnection.Open()
Dim str As String = "ALTER TABLE Items ADD COLUMN Rhour MONEY"
Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
Try
cmd.ExecuteNonQuery()
cmd.Dispose()
myConnection.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
|
|
|
|
|
I have just written a very simple app that displays a form with a single ShockWave Flash control. It runs fine under VS and as a standalone EXE on my development machine (Windows 10 Pro). But when I copy the EXE, associated DLLs, and SWF files to a Windows 7 Pro machine and run it, all I get is a totally white frame. What else do I need to do on the customer machine to make it run?
|
|
|
|
|
Install Flash.
It's bad idea to make an application that's reliant on Flash because the industry is trying to kill it off. It's probably the most insecure piece of software in existence right now.
modified 1-Jun-16 18:00pm.
|
|
|
|
|
Hello geeks
My struggle with Updating data seems never ending. My latest code is as below:
Private Sub StaffInfoAdapterUpdate(ByVal conxnString As String)
Dim cmdText As String
cmdText = "SELECT StaffID, StaffName, blCampuses.CampusName,
Designation, DOB, Gender, NIC, DateOfJoining
FROM tblStaffInfo
INNER JOIN tblCampuses
ON tblStaffInfo.CampusID = tblCampuses.CampusID"
Using Conxn As SqlConnection = New SqlConnection(conxnString)
Dim adapter As SqlDataAdapter = New SqlDataAdapter(cmdText, Conxn)
Dim strUpdate As String = "UPDATE StaffName
SET StaffName = @StaffName " & "WHERE StaffID = @StaffID"
adapter.UpdateCommand = New SqlCommand(strUpdate, Conxn)
adapter.UpdateCommand.Parameters.Add("@StaffName", SqlDbType.VarChar, 50, "StaffName")
Dim parameter As SqlParameter =
adapter.UpdateCommand.Parameters.Add("@StaffID", SqlDbType.Int)
parameter.SourceColumn = "StaffID"
parameter.SourceVersion = DataRowVersion.Original
Dim StaffInfoTable As New DataTable
Try
adapter.Fill(StaffInfoTable)
adapter.Update(StaffInfoTable)
Catch ex As Exception
MsgBox("Error: " & ex.Message)
End Try
End Using
End Sub
When I modify the StaffName for testing, the code does not throw any error, but does not update the record either.
Please note that StaffID is the Primary Key of tblStaffInfo
Please help!
I have a point to clarify in this respect:
Do I have to include ALL the Fields in the strUpdate; which are included in cmdText string; even if most of the fields are least expected to be modified?
|
|
|
|
|
Raabi Anony wrote: Dim StaffInfoTable As New DataTable
Try
adapter.Fill(StaffInfoTable)
adapter.Update(StaffInfoTable)
You create a new DataTable, load the data from the database, and then update the database with the data you've just loaded. There's nothing in that code that changes the data.
Are you missing part of your code from the question?
Raabi Anony wrote: Do I have to include ALL the Fields in the strUpdate; which are included in cmdText string; even if most of the fields are least expected to be modified?
You have to include all of the fields you want to update, along with the primary key. If a field isn't going to be updated, and it's not the primary key, then you don't need to include it.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks Homer, for your attention. I am reading the data in datagridView and modify it there only.
Do I need to make changes for this scenario? Please suggest!
Regards
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
