|
hmm... i cant remember whats the codings for navigating to the 1st record and the last record in the database?
any1 plz help
|
|
|
|
|
BindingManagerBase.Position Property
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemwindowsformsbindingmanagerbaseclasspositiontopic.asp
|
|
|
|
|
hmmm.. i dun quite understand how to add in my codings..
here my codings..
"
'this function is to show Tag number on a textbox
Public Sub loadHardwareTag()
Dim objDataReader As OleDbDataReader
Dim objCommand As OleDbCommand
Dim objCommand2 As OleDbCommand
Dim a As Integer
objCommand = New OleDbCommand
objCommand.Connection = conn
objCommand.CommandText = "select TagNo from Hardware"
objDataReader = objCommand.ExecuteReader
objDataReader.Read()
a = objDataReader("TagNo")
TxtTagNo.Text = a
objDataReader.Close()
"
however this only enable me to view the 1st record on the database. but what i want is to view the last record and +1 to show user what is the next record they gonna enter..
plz help?
|
|
|
|
|
Hi,
I have a table in my Access DB that looks like this:
ID (autonumber)
MyNum1 (number)
MyNum2 (number)
MyStr1 (string)
MyTime (Date/Time)
I've used the Designer to create a typed dataset and a Data Adapter for that table. I added a row to that table and updated the db:
mytable.AddMyDataRow(myrow)
Update( New DataRow() { myrow } )
Because I only want to update that one single row.
I closed my app and re-opened it, and I immediately fill the dataset with the rows from the table, including the one above.
Now, I want to add another row. I do exacly the same thing, and I get an exception when calling Update on the new row:
"Parameter ?_2 has no default value."
The ?_2 changed to ?_1 after I deleted the adapter and re-created it. Dunno why.
I'm really stuck on this. Anyone has an idea what's going on?
Thanks.
/=/=/=/= Sagmam =\=\=\=\
|
|
|
|
|
Without seeing the code that your having a problem with, it's going to be impossible to tell you what wrong. We'd have to see both the code you've written and the code from the "Windows Forms Designer Generated Code" region. Doing everything with the designer makes it difficult to determine what's wrong because your actions don't map to code we can fix directly. It's very often better to write all the database code yourself because you have must greater control over problems like this.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
You're right, of course.
But I'd like to correct myself. I've solved the problem of the ?_X exception (I was trying to update a row of the wrong type). However, I get a different execption:
Concurrency violation: the UpdateCommand affected 0 records.
The problematic table, TaskHistory, is the a table that holds history of a task, so it's a child table of the Tasks table. I've added the proper relationship in the dataset. This is the table definition, as it appears in MS Access Designer:
ID (autonumber)
TaskID (foreign key to the Tasks table)
PersonID (number)
Description (string)
CreationTime (date/time)
This is the code that was generated for the problematic adapter:
'dbaTaskHistory
'
Me.dbaTaskHistory.DeleteCommand = Me.OleDbDeleteCommand4
Me.dbaTaskHistory.InsertCommand = Me.OleDbInsertCommand4
Me.dbaTaskHistory.SelectCommand = Me.OleDbSelectCommand10
Me.dbaTaskHistory.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "TaskHistory", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("CreationTime", "CreationTime"), New System.Data.Common.DataColumnMapping("Description", "Description"), New System.Data.Common.DataColumnMapping("ID", "ID"), New System.Data.Common.DataColumnMapping("PersonID", "PersonID"), New System.Data.Common.DataColumnMapping("TaskID", "TaskID")})})
Me.dbaTaskHistory.UpdateCommand = Me.OleDbUpdateCommand4
'
'OleDbSelectCommand10
'
Me.OleDbSelectCommand10.CommandText = "SELECT CreationTime, Description, ID, PersonID, TaskID FROM TaskHistory WHERE (Ta" & _
"skID = ?) ORDER BY CreationTime DESC"
Me.OleDbSelectCommand10.Connection = Me.dbcTeamTracker
Me.OleDbSelectCommand10.Parameters.Add(New System.Data.OleDb.OleDbParameter("TaskID", System.Data.OleDb.OleDbType.Integer, 0, "TaskID"))
'
'OleDbInsertCommand4
'
Me.OleDbInsertCommand4.CommandText = "INSERT INTO TaskHistory(CreationTime, Description, PersonID, TaskID) VALUES (?, ?" & _
", ?, ?)"
Me.OleDbInsertCommand4.Connection = Me.dbcTeamTracker
Me.OleDbInsertCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("CreationTime", System.Data.OleDb.OleDbType.DBDate, 0, "CreationTime"))
Me.OleDbInsertCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Description", System.Data.OleDb.OleDbType.VarWChar, 0, "Description"))
Me.OleDbInsertCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("PersonID", System.Data.OleDb.OleDbType.Integer, 0, "PersonID"))
Me.OleDbInsertCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("TaskID", System.Data.OleDb.OleDbType.Integer, 0, "TaskID"))
'
'OleDbUpdateCommand4
'
Me.OleDbUpdateCommand4.CommandText = "UPDATE TaskHistory SET CreationTime = ?, Description = ?, PersonID = ?, TaskID = " & _
"? WHERE (ID = ?) AND (CreationTime = ? OR ? IS NULL AND CreationTime IS NULL) AN" & _
"D (PersonID = ? OR ? IS NULL AND PersonID IS NULL) AND (TaskID = ? OR ? IS NULL " & _
"AND TaskID IS NULL)"
Me.OleDbUpdateCommand4.Connection = Me.dbcTeamTracker
Me.OleDbUpdateCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("CreationTime", System.Data.OleDb.OleDbType.DBDate, 0, "CreationTime"))
Me.OleDbUpdateCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Description", System.Data.OleDb.OleDbType.VarWChar, 0, "Description"))
Me.OleDbUpdateCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("PersonID", System.Data.OleDb.OleDbType.Integer, 0, "PersonID"))
Me.OleDbUpdateCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("TaskID", System.Data.OleDb.OleDbType.Integer, 0, "TaskID"))
Me.OleDbUpdateCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_ID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_CreationTime", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CreationTime", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_CreationTime1", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CreationTime", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_PersonID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PersonID", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_PersonID1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PersonID", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_TaskID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "TaskID", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_TaskID1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "TaskID", System.Data.DataRowVersion.Original, Nothing))
'
'OleDbDeleteCommand4
'
Me.OleDbDeleteCommand4.CommandText = "DELETE FROM TaskHistory WHERE (ID = ?) AND (CreationTime = ? OR ? IS NULL AND Cre" & _
"ationTime IS NULL) AND (PersonID = ? OR ? IS NULL AND PersonID IS NULL) AND (Tas" & _
"kID = ? OR ? IS NULL AND TaskID IS NULL)"
Me.OleDbDeleteCommand4.Connection = Me.dbcTeamTracker
Me.OleDbDeleteCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_ID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "ID", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_CreationTime", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CreationTime", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_CreationTime1", System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "CreationTime", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_PersonID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PersonID", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_PersonID1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "PersonID", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_TaskID", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "TaskID", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand4.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_TaskID1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte), "TaskID", System.Data.DataRowVersion.Original, Nothing))
The following code sets the CreationTime field for all Added child rows to Now, and then tries to update the db. The Update command at the end throws the execption. Assume that there are currently 2 rows in the dataset: the first is Unchanged and the second is Added.
Dim AllRows = row.GetTaskHistoryRows
Dim rowHist As dsData.TaskHistoryRow
For Each rowHist In AllRows
If rowHist.RowState = DataRowState.Added Then
rowHist.CreationTime = Now.UtcNow
End If
Next
dbaTaskHistory.Update(AllRows)
where 'row' is the parent row from the Tasks table.
But since I got an exception, I tried to update each added record seperately like this:
Dim rowHist As dsData.TaskHistoryRow
For Each rowHist In row.GetTaskHistoryRows
If rowHist.RowState = DataRowState.Added Then
rowHist.CreationTime = Now.UtcNow
dbaTaskHistory.Update(New DataRow() {rowHist})
End If
Next
And it worked. However, it's not acceptible that I perform multiple updates for all history records - it's just too much and I don't see why I should. Unless, of course, it has something to do with the autonumber key, but I doubt it.
I hope this time I've provided enough info.
Thanks...
/=/=/=/= Sagmam =\=\=\=\
|
|
|
|
|
Another small update: I found out that it's actually the OTHER row (not the Added one) that caused the "0 records" exception! I tried to update it seperately, and I got the exception. Also, I believed it was Unchanged, but I was wrong - it was Modified. This leads me to the next question: what if the row was SUPPOSED to be modified? why would the update fail with "0 records" on such a row??? I do need the option to update existing records  ...
thanks.
/=/=/=/= Sagmam =\=\=\=\
|
|
|
|
|
I was wondering if anybody knows why the following occurs:
If you write the following code the blnCheck = True
Dim blnCheck as Boolean<br />
blnCheck = "TEDDY" Like "*EDD*"
But if you use the variable strFind the blnCheck = False
textFind.text = "EDD"<br />
Dim blnCheck as Boolean<br />
Dim strFind as String = "*" & UCase(txtFind.text) & "*"<br />
blnCheck = "TEDDY" Like "strFind"
This is not the actual code I am having trouble with, but it demonstrates the problem in an abbreviated form.
Thanks
Andy S.
|
|
|
|
|
That's because Like is an operator and works on 2 String operands.
Result = <String> Like <String>
It's no different than if you used Result = 34 + 23.
Dicescuz wrote:
Dim strFind as String = "*" & UCase(txtFind.text) & "*"
blnCheck = "TEDDY" Like "strFind"
This code will compare the String TEDDY to the String strFind and, of course, the result will be False.
I think your looking for
blnCheck = "TEDDY" Like strFind
Here, strFind evaluates to a String, *whatevertext*. In your previous statement, since you enclosed strFind in quotes, it evaluated to the String strFind, not the contents of strFind.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Dave you are the Man. Thanks to everyone on this site that has helped me. The Find Funtionality that I was working on now works. Great people, and great help!!!!!
|
|
|
|
|
The user saves an excel file to the system, then I grab that save directory path of that file. That much is done. I want to know how to open that excel file and send all the records (row by row or however) into a SQL Server table. I think I already have a connection established.
I don't want to use DTS, it should be done programmatically.
Thank you in advance for any help!
|
|
|
|
|
You're going to have to use the Excel Automation library to open Excel, load your sheet, and parse each line of your sheet and add it to the database yourself. You'll have to write the SQL statements, build the database command object and parameters, then assign the data from each cell to the appropriate parameter object, and finally execute it. You'll have to do this for each and every line of the Excel sheet you want to add to the database.
This[^] little article should get you started on Excel Automation. It does NOT cover any database code.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
That example would help me get from sql to populate an excel sheet, but I'm looking for the opposite order. I've got an excel file saved, now I can get the excel file to open. Now I just need to have maybe a loop go through taking each row from the xl file and shooting it into an existing sql table.
Thank you for your help.
|
|
|
|
|
Sorry i just read the title and its obvious that you are using vb.net but this should actually still work, i have it working in both, but this is a very cut down copy of what i've used as what i am doing is completely different
Not sure if this will help but if it is VB6 you are using it might so let me know, cheers
Private Sub Form_Load()
Dim oExcel As Object
Dim oWB As Object
Dim ows As Object
Dim oRS As New ADODB.Recordset
Dim i As Long
Dim newI As Long
'ammended
Dim oConn As New ADODB.Connection
oConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=tempdb;Data Source=JETSTREAM"
oConn.Open
'Set yourself up a Table with all the fields required and fill a recordset
oRS.Open "SELECT * FROM TempTable", oConn, adOpenKeyset, adLockOptimistic
'Create yourself the excel application
Set oExcel = CreateObject("Excel.Application")
Set oWB = oExcel.Workbooks.Open(PathtoASpreadSheet)
Set ows = oWB.Worksheets("Sheet1")
'Loop through the first column until you hit the first blank cell
'This is a quick and dirty way there is probably a better way out there.
'ammended can't remember bloody overflow thing, also can't remember if i have the cells
'reference (i, A) round the right way.
For i = 1 To 65535
If ows.Cells(i, "A") = "" Then
newI = i
Exit For
End If
Next
'Then loop through every column and every cell adding the data to the recordset
'in the correct fields
For i = 1 To newI
'Can't remember if .value is required for the cells.
oRS.AddNew
oRS.Fields("FirstField").Value = ows.Cells(i, "A").Value
oRS.Fields("SecondField").Value = ows.Cells(i, "B").Value
'etc, etc,
oRS.MoveNext
'updates the recordset and the table
oRS.Update
Next
"If i was king cigarettes would be free."
|
|
|
|
|
I already have a connection established, the following is code that I came up with that opens the .xls that I am after. I just don't know how to finish this code off to loop through the XL rows and put those records into a SQL table.
Public Sub Batch(ByVal Path As String)
Dim objExcel As New Excel.Application()
Dim objWrkBk As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim objRng As Excel.Range
objWrkBk = objExcel.Workbooks.Open(Path)
End Sub
Thank you for any continued support.
|
|
|
|
|
That stuff you emailed me looks much cleaner, thank you.
I'm having trouble with the connection string though:
oConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=TempExcel;Data Source=JETSTREAM"
I have a windows auth to connect, in other words, I don't need to enter username or password to get in. I'm not sure how to adjust this.
Also,
in "SELECT * FROM TheTable", what is TheTable?
Is it some name from the .xls somehow?
Thank you for your patience.
|
|
|
|
|
Hey All,
If anyone can tell me a good book\web-site that can help me build a visual component..
Can I add my new component to the toolbox window?
Thanks
Roy.
|
|
|
|
|
I don't know of any book that just covers components or even comes close to covering every aspect of component development. You can start with Visual Basic(r).NET: The Complete Reference[^] by Jeff Shapiro. It did pretty well for me.
As for your second question, of course you can add your own controls to the ToolBox. All you need to do is right-click the ToolBox window somewhere and select Add/Remove Item...
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I am new to vb.net.
I have created a form using vb.net.
The form has the following fields and a Datagrid:
a. Revision
b. Installation
c. Part No
d. Qty
e. Description
f. Change Rev (Field is Disable - autofill based on criteria)
The form also has the command buttons: Save and Close
For initial revision "-", I am able to capture and saved what was key-in the above fields (except "Change Rev").
For making subsequent revision, "A" (for example), the previous revision data is recaptured onto the fields.
However, I have a problem. Is it possible when there is any changes made to any of the following field (Part
No, Qty and Description) that is different from the previous revision, the field "Change Rev" will auto-fill
the current revision "A" in Change Rev. The purpose is to tell the user which set of data is change and in
what revision, if there is no change make to data, the Change Rev remain as "".
Any advise how to approach the above is appreciated. Many Thanks.
|
|
|
|
|
Hi,
I'm working on the VB.NET DDE project. I'm facing the trouble to establish a DDE connection. Does anybody here has an experience with the DDE for VB.NET?
If yes, could you share, please.
Many thanks,
Valdo
|
|
|
|
|
VB.NET doesn't support DDE at all. All support for DDE in VB ended with VB6. You'll have to P/Invoke all of the Win32 API functions and redefine all the structures your going to need. Docs on NetDDE begin here[^]. But be warned, all the example code is written in C++ and would need to be converted to VB.NET. There are various musings on the 'Net concerning DDE and VB.NET, but nothing extensive. You can look at the search results here[^].
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
Hi
Can anybody tell me how can I have a treeview like window explorer, If one client updates (adds any file to it) other clients on same or different machine will reflect the change
Thanks
Vikas...
|
|
|
|
|
Ewww.. Difficult to implement. Your going to have to have some kind of central server that each of your clients connects to so it can relay changes back and forth.
Windows Explorer can do this because it monitors for changes in the file system on the server. Your app is not going to have this little blessing because your not looking at a file system.
Or are you? In that case, you can use the FileSystemWatcher[^] class in VB.NET. There is no equivilent in VB6.
RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
|
|
|
|
|
I'm building a multi-project solution in VB.Net and it tells me that 1 failed. How do I locate what project and where in that project is causing the failure?
|
|
|
|
|
The task list will usually list out any errors discovered. You can open it using CTRL-ALT-K or going to View | Other Windows | Task List to open the window. You can then double click on any error and it will automatically open up the offending file.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
