|
GOOD Morning Dimuthu
First thing First, Before you place a command text in your command Object
cmd1.CommandText = "Delete * FROM NutrientComp"
you should go to your SQlquery analyzer and check if your SQL statement is correct. i dont remember seing a statement that looks like
Delete * FROM Table
We dont do it like that.
we say
Delete NutrientComp
where Field = this
Or if you want to Delete the whole contents of the Table we use Truncate like this.
Truncate Table NutrientComp
Number 2: while you use ExecuteNonQuery function, you dont include any connection.
see this
Dim SelectText as String = "Select * from myTable"
Dim SqlConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My_Project\test.mdb
Dim myDA As oledb.oledbDataAdapter= New oledb.oledbDataAdapter(SelectText, SqlConn)
Hope it helps
Vuyiswa Maseko
|
|
|
|
|
Hello Agian everyone !
can someone point me the way how to fast searching data in xml with VB.NET/C# ?
i need urgently.
|
|
|
|
|
Anatha wrote:
i need urgently.
Yeah, a lot of that seems to be going around.
How much XML ? Your best bet is the XmlDocument class and XPath, almost certainly
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Hi, currently I am developing a database application in vb2005 with ms access as a database back end. In my Ms access there is one table name tblProgBudget with the following field:
- ProgID (Number)<br />
- Year (Number)<br />
- ProgBudYear (AutoNumber)<br />
- GovContrib (Currentcy)<br />
- OrgContrib (Current)
I write a code in vb 2005 try to insert new record into this table (I add to all field except ProgBudYear because this field is autonumber field so I want the system to generate it by automatically). The below is my code:
Private Sub btnAddProg_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddProg.Click
' Check whether the user select any organization name.
If cboSelOrgName.Text <> "" Then
' Check whether the user select any program/project name.
If cboSelProgName.Text <> "" Then
' Check whether the user enter a correct year format.
If Len(txtYearBudPlan.Text) = 4 Then
' Declare a new command to get program/project id.
Dim cmdGetProgID As New OleDbCommand
With cmdGetProgID
.Connection = cnnDonorDb
.CommandType = CommandType.Text
.CommandText = "SELECT tblProgName.ProgID FROM tblProgName WHERE tblProgName.OrgName LIKE '" & cboSelOrgName.Text & "' AND tblProgName.ProgName LIKE '" & cboSelProgName.Text & "'"
End With
' Open the connection.
Try
cmdGetProgID.Connection.Open()
Catch exFileNotFound As System.Data.OleDb.OleDbException
MessageBox.Show("The system has a problem to connect to your database system." & ControlChars.CrLf & "Please verify that your database is located in the following path: 'C:\DICDonorDb\DICDornorDb.mdb'", "Database file not found", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Try
Dim drdGetProgID As OleDbDataReader = cmdGetProgID.ExecuteReader
If Not drdGetProgID Is Nothing Then
drdGetProgID.Read()
' Get program/project id from the system.
strProgID = drdGetProgID(0).ToString
' Close the connection
cmdGetProgID.Connection.Close()
' Add an information on the form to the program/project budget.
' Declare the new command.
Dim cmdAddProgBudInfo As New OleDbCommand
With cmdAddProgBudInfo
.Connection = cnnDonorDb
.CommandType = CommandType.Text
.CommandText = "INSERT INTO tblProgBudget (tblProgBudget.[ProgID], tblProgBudget.[Year], tblProgBudget.[GovContrib], tblProgBudget.[OrgContrib]) VALUES (" & Val(strProgID) & ", " & Val(txtYearBudPlan.Text) & ", " & Val(txtGovCont.Text) & ", " & Val(txtOwnCont.Text) & ")"
End With
' Open the connection.
Try
cmdAddProgBudInfo.Connection.Open()
Catch exFileNotFound As System.Data.OleDb.OleDbException
MessageBox.Show("The system has a problem to connect to your database system." & ControlChars.CrLf & "Please verify that your database is located in the following path: 'C:\DICDonorDb\DICDornorDb.mdb'", "Database file not found", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End Try
' *********************************************
'
' PROBLEM REMAIN IN HERE
'
' *********************************************
' Add an information to the database.
cmdAddProgBudInfo.ExecuteNonQuery()
' Close the connection.
cmdAddProgBudInfo.Connection.Close()
' Clear an information from the variable.
strProgID = ""
' Inform the user after success add an information into the database system.
MessageBox.Show("The program/project budget for the year " & Trim(txtYearBudPlan.Text) & " and its relevant information are successfully added into the database system." & ControlChars.CrLf & "The system will clear an information on the form in order to prepare to add another year of program/project budget.", "Add Year of Program/Project Budget", MessageBoxButtons.OK, MessageBoxIcon.Information)
' Clear an information on the form.
txtYearBudPlan.Text = ""
txtGovCont.Text = ""
txtOwnCont.Text = ""
ElseIf drdGetProgID Is Nothing Then
MessageBox.Show("The program/project '" & cboSelProgName.Text & "' from '" & cboSelOrgName.Text & "' organization does not exist in the database system." & ControlChars.CrLf & "Please verify your an information of this program/project again.", "Program/Project not exixt in database", MessageBoxButtons.OK, MessageBoxIcon.Stop)
End If
Else
MessageBox.Show("The year of budget plan require you to enter four digit format." & ControlChars.CrLf & "Example: This year is " & Format(Year(Now()), "YYYY") & ".", "Invalid Year Digit", MessageBoxButtons.OK, MessageBoxIcon.Stop)
txtYearBudPlan.Focus()
End If
ElseIf cboSelProgName.Text = "" Then
MessageBox.Show("You didn't select any program/project.", "Require Selected Program/Project", MessageBoxButtons.OK, MessageBoxIcon.Stop)
End If
ElseIf cboSelOrgName.Text = "" Then
MessageBox.Show("You didn't select any organization name.", "Require Selected Organization", MessageBoxButtons.OK, MessageBoxIcon.Stop)
End If
End Sub
When I try to run, there is an except occure as the below detail technical information:
System.Data.OleDb.OleDbException was unhandled<br />
ErrorCode=-2147217900<br />
Message="The INSERT INTO statement contains the following unknown field name: 'tblProgBudget.ProgID'. Make sure you have typed the name correctly, and try the operation again."<br />
Source="Microsoft JET Database Engine"<br />
StackTrace:<br />
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)<br />
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)<br />
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)<br />
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)<br />
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()<br />
at DIC_Donor_Database.frmProgBudget.btnAddProg_Click(Object sender, EventArgs e) in C:\Documents and Settings\roathkanel\My Documents\Visual Studio 2005\Projects\DIC Donor Database\DIC Donor Database\frmProgBudget.vb:line 182<br />
at System.Windows.Forms.Control.OnClick(EventArgs e)<br />
at System.Windows.Forms.Button.OnClick(EventArgs e)<br />
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)<br />
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)<br />
at System.Windows.Forms.Control.WndProc(Message& m)<br />
at System.Windows.Forms.ButtonBase.WndProc(Message& m)<br />
at System.Windows.Forms.Button.WndProc(Message& m)<br />
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)<br />
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)<br />
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)<br />
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)<br />
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)<br />
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)<br />
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)<br />
at System.Windows.Forms.Application.Run(ApplicationContext context)<br />
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()<br />
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()<br />
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)<br />
at DIC_Donor_Database.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81<br />
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)<br />
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)<br />
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()<br />
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)<br />
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)<br />
at System.Threading.ThreadHelper.ThreadStart()
I feel that I enter the correct table and field name (because it copie the table name and field name from the database). But I can't run this code successfully.
Please give me some advice!!!
|
|
|
|
|
Try just listing the columns without the table name. Also, your strings don't have quotes around them, this will cause an error.
Finally, your database is totally insecure, you need to read up on SQL injection attacks. And, even though this may be a very small app, I'd still advocate architecting it with a proper data layer.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Christian Graus wrote: Try just listing the columns without the table name. Also, your strings don't have quotes around them, this will cause an error.
I try to remove as your suggestion. The below is the statement that I just remove:
Dim cmdAddProgBudInfo As New OleDbCommand
With cmdAddProgBudInfo
.Connection = cnnDonorDb
.CommandType = CommandType.Text
.CommandText = "INSERT INTO tblProgBudget (ProgID, Year, GovContrib, OrgContrib) VALUES (" & Val(strProgID) & ", " & Val(txtYearBudPlan.Text) & ", " & Val(txtGovCont.Text) & ", " & Val(txtOwnCont.Text) & ")"
End With
But it still display an error with the below exception:
System.Data.OleDb.OleDbException was unhandled<br />
ErrorCode=-2147217900<br />
Message="Syntax error in INSERT INTO statement."<br />
Source="Microsoft JET Database Engine"<br />
StackTrace:<br />
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)<br />
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)<br />
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)<br />
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)<br />
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()<br />
at DIC_Donor_Database.frmProgBudget.btnAddProg_Click(Object sender, EventArgs e) in C:\Documents and Settings\roathkanel\My Documents\Visual Studio 2005\Projects\DIC Donor Database\DIC Donor Database\frmProgBudget.vb:line 182<br />
at System.Windows.Forms.Control.OnClick(EventArgs e)<br />
at System.Windows.Forms.Button.OnClick(EventArgs e)<br />
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)<br />
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)<br />
at System.Windows.Forms.Control.WndProc(Message& m)<br />
at System.Windows.Forms.ButtonBase.WndProc(Message& m)<br />
at System.Windows.Forms.Button.WndProc(Message& m)<br />
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)<br />
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)<br />
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)<br />
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)<br />
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)<br />
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)<br />
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)<br />
at System.Windows.Forms.Application.Run(ApplicationContext context)<br />
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()<br />
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()<br />
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)<br />
at DIC_Donor_Database.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81<br />
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)<br />
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)<br />
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()<br />
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)<br />
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)<br />
at System.Threading.ThreadHelper.ThreadStart()
I think the cause of the problem is the value of the autonumber field. I don't know how to set it.
|
|
|
|
|
Let me help you, you have a small problem.
Tell me is the Field "ProgID" an identity Column in your Table?if so state it and i will help you .
Thanks
Vuyiswa Maseko
|
|
|
|
|
Vuyiswamb wrote: Tell me is the Field "ProgID" an identity Column in your Table?
What do you mean identity column? Let go back to my orignial post. The field ProgID and Year were set as a primary key (mean the data in both column) could not be duplicate.
I try to rename the column to the other name, but the system still have an error with the respective column name as the above error that i post.
Hope to see your reply soon!!!
|
|
|
|
|
I went back to your main post.
1st your table is not structured well.
2nd you have to know the Purpose of a Primary key.
3th you have a Column ProgID and you made a Primary key.and had ProgBudYear and made it an Identity Column(AutoNumber). ProgID is a column that serves no Purpose according to view, because there might be dublicate as you suspected. Now i suggest you make ProgBudYear a Primarykey, and keep it as an autoNumber and remove ProgID if you only Put it in your table for the purpose of Primary key.
Then your table will look like this
if ProgBudYear is an identity table then you will not have trouble with duplicates and will make ProgBudYear a Primary key
- ProgBudYear (AutoNumber)
- Year (Number)
- GovContrib (Currentcy)
- OrgContrib (Current)
and when you insert your query should be something like this
insert into mytable
values(1981,14000,20000)
Exclude Identity column.
Hope this help
Thanks
Vuyiswa Maseko
|
|
|
|
|
So what does the CommandText look like just before you execute it??
Also, get rid of the Val functions. In this case, your converting a string to a value then back to a string.
Actually, rewrite that code to use parameterized queries and you won't have to worry about getting every single quote mark correct. It'll make your code more robust and easier to maintain.
|
|
|
|
|
Would someone please explain the concept of Delegates in a way that is a little easier to understand. The following is what I have seen in a tutorial on Delegates.
'This is how we declare a Delegate in a form class
Private Delegate Sub MyDelSub()
'Then we use the Delegate by simply declaring a variable of the Delegate and assigning the sub or function to run when called. First the sub to be called:
Private Sub WriteToDebug()
Debug.WriteLine( "Delegate Wrote To Debug Window" )
End Sub
What I don't understand is the line Private Delegate Sub MyDelSub(). Would you please explain that line a little bit? Is the word Delegate a key word in VB.NET like the words String, Function etc.? Also what is MyDelSub()? What I'd like to know in more detail are when and how to use Delegates, thank you in advance for your help.
-- modified at 20:15 Wednesday 22nd August, 2007
|
|
|
|
|
ASPnoob wrote: Is the word Delegate a key word
yes
ASPnoob wrote: Also what is MyDelSub()?
The name of the delegate
You'd use delegates when you want to fire a method that calls an arbitrary number of clients, or when yuo want to cal a method in another class based on an event.
So, MyDelSub is now a type - it's a delegate which takes no arguments and doesn't return anything. Now you can do this:
Private MyDelSub OnDelete
Now, in your class methods you could do this:
if (OnDelete is not nothing ) then OnDelete()
And, you can hook OnDelete to call any method in any class you like, so long as it has this signature.
Events, such as a click event, work this way, your derived class subscribes to the delegate, and it is called when the button is clicked.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Hi,
I need to search for a particular phrase in a loaded page in a webbrowser and then highlight all instances of it...I know i can search thru it via the document body, but how do i highlight it to a specific color?
Thanks in advance,
Ahmad
|
|
|
|
|
This might be doing it the hard way (if there is an easier way)...
Take the text. insert the appropriate html tags around each word to highlight it and then replace the document body with the new string that includes the tags...
|
|
|
|
|
Hi,
I need to display a data set in some sort of table format, which will be scrollable..
This set contains approximately 500 rows of data items, say contact information.
Some rows need to be in a different color than the other rows according to some criteria which is why I can't use a big textbox.
I was thinking about several ways to implement this:
1. Using a ListView Control
2. Using a DataGrid Control
3. Using a Picture box and simply painting the item information on it. I have no need to interact with the data just display it.
4. Using a TableLayooutPanel and filling it with labels.
Which of these ways is the most efficient way to do this, performance wise, least memory requirements, best application responsiveness etc?
Thanks,
Gary
|
|
|
|
|
Hi,
I only know some of these Controls, so here is a partial answer:
A PictureBox is not the solution; it does not help you whatsoever, the only
thing it tries to do reasonably well is showing an existing IMAGE. It is not
intended to be drawn upon (a Panel would fit that).
I would not choose the ListView, I expect it is tailored to the Explorer
functionality, and either too complex or not really fitting your needs.
Hence my choice would boil down to DataGrid vs. TableLayoutPanel
Hope this helps.
Luc Pattyn [Forum Guidelines] [My Articles]
this weeks tips:
- make Visual display line numbers: Tools/Options/TextEditor/...
- show exceptions with ToString() to see all information
- before you ask a question here, search CodeProject, then Google
|
|
|
|
|
Not really sorry,
A picture box provides a graphics object that you may use to draw paint
whatever you like on, text included hence it can be used to show a table of data.
I may have neglected to mention this: If this data was static then I wouldn't even bother asking the question. New data is constantly received and displayed in new lines while older lines are deleted, this is my concern with performance.
The update rate is fast perhaps once a second.
Updating a ListView control therefore might seem more efficient since it only requires two actions: add and remove item. Updating the tablepanel will take a loop of 500*NumberOfColumns actions.
Which one of the ways I have listed is likely to take the least amount of CPU time?
|
|
|
|
|
errorfunktion wrote: A picture box provides a graphics object that you may use to draw paint
whatever you like on
No, it doesn't. Every control that's visible has a canvas you can paint on, not the jsut the PictureBox control. The PictureBox was designed to just show an image. It loads an image and paints that image on itself.
For your situation, the ListView control would probably be the most appropriate. You'd have to remember to remove items at the beginning of the list if they are no longer needed since adding more and more items (without removing any!) will take long and longer to paint.
|
|
|
|
|
I'm having problems with looping thru a datagrid to get calculations for each record. Help with my for loop will be greatly appreciated.
Dim intRec As Integer
'My Issue is Here.
For intRec = 0 To dgOrderReport.DataSource
Select Case DatePart("w", d1)
Case 1
subtract = subtract + 1
Case 7
subtract = subtract + 2
End Select
Select Case DatePart("w", d2)
Case 1
subtract = subtract + 2
Case 7
subtract = subtract + 1
End Select
absday = DateDiff("d", d1, d2) + 1 - subtract
fncGetDays = absday - CInt(2 * (absday \ 7))
If fncGetDays < 0 Then fncGetDays = 0
intRec += 1
Next
Catch ex As Exception
MsgBox(ex.Message)
End Try
Thank You<code></code>
|
|
|
|
|
I cannot figure out what you're trying to do...But I do notice that you've got a For loop and you never use the counter inside of the loop for anything.
For intRec = 0 To dgOrderReport.DataSource
needs to be changed to
For intRec = 0 To dgOrderReport.DataSource.Rows.Count - 1
just for starters
|
|
|
|
|
Basically what I'm trying to do is - subtract a time field in my record from my system time for each record. Business Days Only
example
order NO Name Req_date Days late
11111 Test 8/17/2007 3
Days Late is a dynamic column
I need to do this for each record - and I can not get my loop to work - when it loops it I only get the calculation for the first record and it puts that calculation in every record. Thanks For the Reply - I've changed it to a dataset so that I can use rows.count-1 -- This is what I have
For intRec = 0 To MyDataSet.Tables(0).Rows.Count - 1
Select Case DatePart("w", d1)
Case 1
subtract = subtract + 1
Case 7
subtract = subtract + 2
End Select
Select Case DatePart("w", d2)
Case 1
subtract = subtract + 2
Case 7
subtract = subtract + 1
End Select
absday = DateDiff("d", d1, d2) + 1 - subtract
fncGetDays = absday - CInt(2 * (absday \ 7))
If fncGetDays < 0 Then fncGetDays = 0
intRec += 1
Next<code></code>
Thank You
|
|
|
|
|
somewhere in here you need to have something to the effect of
MyDataSet.Tables(0).Rows(intRec).Item("Days Late") = value
where do you assign this value? In one of the functions?
|
|
|
|
|
' MyDataSet.Tables(0).Rows(intRec).Item("Days Late") = value '
This is the value that I'm trying to find for each record which I believe I am getting with ' fncGetDays = absday - CInt(2 * (absday \ 7)) '
after the date calculations
I'm getting that value within that loop? Should I or should I not do it that way?
I think that should be fine
How do I get the loop to go to the next record after getting the value for the first record and so on?
Thank You
|
|
|
|
|
Now that you have a dataset you can easily use the datarow approach:
<br />
Dim rw As DataRow<br />
For each rw in MyDataSet.Tables(0).Rows<br />
MsgBox(rw.Item("Days Late"))<br />
Next<br />
This will loop through each row, and will stop once there are no more rows.
Ofcourse you'll want to use rw.Item in something other than a MsgBox...
Good luck,
Johan
My advice is free, and you may get what you paid for.
|
|
|
|
|
Thanks -
I'm still only getting the calculations for the first record - what in world am I missing?
Should I put this
Dim rw As DataRow
For each rw in MyDataSet.Tables(0).Rows
rw.Item("Days Late"))
Next
within my current for loop or should I just get rid of what I have and just use
the approach above?
Thank You
|
|
|
|
|