|
lahsiv wrote:
If rs1.State = 1 Then rs1.Close()
Where did people develop the habit of using global variables for recordsets and using 'lazy close'? I've had to deal with such code, but I wonder how such sloppy coding came into common practice.
Doing things with a datareader is really pretty easy. Opening a connection by creating a new Odbc.OdbcConnection(connectString). Then create a new Odbc.OdbcCommand(theCommandString, theConnectionObject). Rather than appending string arguments, insert them using "@@" syntax, e.g. Dim MyCmd as New Odbc.OdbcCommand("SELECT User_Password from UserTable where User_ID=@@UserID", myConnection). Then fill in the parameters using the Cmd.Parameters.AddWithValue(name, value) method, e.g. Cmd.Parameters.AddWithValue("@@UserID", Trim(Textbox1.Text)).
Once the command object is loaded with parameters, you may use Cmd.ExecuteNonQuery() to perform a command that won't return anything, Cmd.ExecuteScalar() to perform a command that will return one object, or Cmd.ExecuteReader() to return an iDataReader object. Use theReader.read() to fetch each record (including the first) into the reader object; it will return false on EOF. After each record is read, you may use theReader(0) to read the first field, theReader(1) to read the second, etc. If your query is properly designed, you will know in advance how many fields will be returned and what they will be.
modified on Monday, August 18, 2008 6:37 PM
|
|
|
|
|
Excellent response, but he does not want to use datatables and datasets, he knows recordsets and is unhappy because MS has changed from the VB6 methods 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The approach I described works pretty similarly to recordsets for reading. The syntax is different, and one does a 'read' before each record instead of a 'movenext' after, but otherwise things are pretty similar. It does not use datatables or datasets.
I'm not sure what the nicest way is to do updates without learning to use DataWriter objects, though. Using INSERT and UPDATE queries isn't particularly difficult, but it doesn't seem like the fastest thing in the world.
|
|
|
|
|
supercat9 wrote: DataWriter objects
Whats a datawriter?
supercat9 wrote: Using INSERT and UPDATE queries isn't particularly difficult
How else do you get the data to and from a database?
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Whats a datawriter?
(blush) When I started using DataReaders, I thought there was a corresponding class of DataWriter objects I simply didn't understand. Having looked through the documentation, it seems that I was confusing read/write DataAdapters with DataWriters, as I don't think the latter concept exists.
Mycroft Holmes wrote: How else do you get the data to and from a database?
I think there exists some means of doing bulk-data inserts into a cursor or something. Using one INSERT or UPDATE query per record seems to run rather slowly, even when I combine multiple queries into a single command. BTW, I know this is the VB forum rather than the SQL forum, but I'll ask anyway: is there a good way to specify, when inserting data into a table with a uniqueness constraint, that new data should overwrite old data? For example, if I'm updating a record associated with a hardware token (so I know that no other task will simultaneously update the same record) two approaches that would be workable are:- Delete the old record and write a new one.
- Try an 'Update' on the serial number, and if no records were updated, do an 'Insert'.
The first approach is rather ugly and creates unnecessary work at the server removing and re-adding data to the indexes. The second approach would be cleaner except that it doesn't allow for easy consolidation of multiple updates.
|
|
|
|
|
supercat9 wrote: doing bulk-data inserts into a cursor or something
There is a bulk copy method which ONLY inserts data, not sure which tech it uses under the hood. This is excellent for loading voluminous data. There is no bulk update facility except using a query.
supercat9 wrote: is there a good way to specify,
Part of the utilities I use is a code builder that creates 2 stored procs for every table, it assumes that the 1st field in the table is an ID/identity field (unique constraint). The DAL passes in a 0 or record number, if the IS is 0 then the proc does the update else it inserts. You can also use a various strategies in the proc to detect the existence of a unique constraint (select count(*) where... or if exist select * from ...).
Never do the delete/insert thing, always do the update and have an index on the unique field. Why, because the loookup to delete the record can cost more than the update especially if there are foreign keys, and then you get the insert for free.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi Folks I am Using Visual Basic within Excel and have encountered a problem I hope someone can help with
I have a table of Bills and months ie
Month1 Month2 ........
Bill 1 0 0
Bill 2 0 0
Bill 3 0 0
For each bill there is a value in the month column.
Each Cell starts with a value of 0
I create a NEW worksheet ie - NEWSHEET and this passes a formula back to cell C3 on sheet Bills
C3 before = 0
C3 after = NEWSHEET!L5 - showing the contents of Cell L5 ON Sheet NEWSHEET
When I delete the NEWSHEET for whatever reason the value of C3 changes to this:-
C3 becomes =#REF!L5
In itself this isn't a problem as I was expecting this to happen however once the cell contains =#REF!L5
I want to run a small script to look at each cell and if the value of the cell is =#REF!L5 then I want to reset the value to zero
if the value of the cell isn't =#REF!L5 then I wish to leave the value alone
Hope this makes sense and that someone can assist
|
|
|
|
|
Look up functions IsNA, IsError... you can use those to check the cell values and modify contents if needed.
HTH
|
|
|
|
|
Hi all,
first i stored employee name into an array from start_cell_number to end_cell_number like this
Dim testarray() As String = New String(47) {}
For i = start_cell_number To end_cell_number mean (4 to 8)
testarray(i) = (dr("User_Name").ToString())
Next
then tried to findout total number of user name repeated in an array like this
Dim noOfTimes As Integer = 0
For Each str As String In testarray
If str = Label1.Text Then
noOfTimes = noOfTimes + 1
End If
Next
here iam getting total number of user name repeates in an array but when iam putting below line in loop block iam getting result like this
Response.Write(noOfTimes.ToString() + "" + Label1.Text)
00001234444444444444444444444444444444444444444444444444444 but i have to need result like this
00001234000000000000000000000000000000000000000000000000000 becuase username only repeated 4 times.
kindly suggest me where iam doing mistake.
thanks in advance
Regards
Rameez
|
|
|
|
|
Hi
When you are filling your array
you are not doing a dr.next() to get the next record?
you have to be careful when your are using this !
e.g
while (dr.next())
{
//make something
}
|
|
|
|
|
kindly explain in little bit detail what i have to do .
dr.next() where i have to use . dr(is table row here not data reader) in my case.
Here is my code
For Each dr In dt.Rows
' Dim Myarray As String
a = (dr("StartDateTime").ToString())
Dim timeSpanDiff1 As TimeSpan = a.Subtract(start_display_datetime) '
Dim mins1 As Integer = timeSpanDiff1.Hours * 60 + timeSpanDiff1.Minutes '//total minutes
start_cell_number = mins1 / 15 '
' Label1.Text = start_cell_number
'Response.Write(start_cell_number.ToString() + "</br>")
b = (dr("EndDateTime").ToString())
Dim timeSpanDiff2 As TimeSpan = b.Subtract(start_display_datetime) Dim mins2 As Integer = timeSpanDiff2.Hours * 60 + timeSpanDiff2.Minutes
end_cell_number = mins2 / 15 '//count number of cells
Label1.Text = (dr("User_Name").ToString())
For i = start_cell_number To end_cell_number
testarray(i) = (dr("User_Name").ToString())
Next
Next
<code> Dim noOfTimes As Integer = 0
For Each str As String In testarray
If Str = Label1.Text Then
noOfTimes = noOfTimes + 1
End If
Next
Response.Write(noOfTimes.ToString() + "" + Label1.Text)</code>
For d = 0 To 31
Dim tbcell As TableCell = New TableCell
If testarray(d) = Nothing Then
tbcell.BackColor = Color.Yellow
Else
tbcell.BackColor = Color.Green
tbcell.ColumnSpan = noOfTimes
End If
tbcell.Text = " "
trow.Cells.Add(tbcell)
Next
Table1.Rows.Add(trow)
Next
please check this that where iam doing mistake .
Regards
Rameez
modified on Monday, August 18, 2008 1:09 AM
|
|
|
|
|
i want to Store and retrieve data from web server (internet) from my desktop application which i developed in vb.net
can anyone give me the solution how to do this task.
|
|
|
|
|
You have to use "WebService" or "WCF", if you are on .NET 3.0.
|
|
|
|
|
hi Christian
Actually i want to store and retrieve data into my sql which is on web server.
Is it okey if i pass id address and userid,password in connection string in .net application?
|
|
|
|
|
ershad wrote: Is it okey if i pass id address and userid,password in connection string in .net application?
If your app is used on the same network as your users, or if your SQL Server is actually exposed so anyone can connect to it and run SQL on it ( a colossaly bad idea ). then yes.
Most intelligent people would write a webservice to do this.
Christian Graus
No longer a Microsoft MVP, but still happy to answer your questions.
|
|
|
|
|
Christian Graus wrote: Most intelligent people would write a webservice to do this.
I second that.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hii
Add the Web reference in your project and create the instance of web reference and use the methods of your web service.
Thanx
~Khatri Mitesh
|
|
|
|
|
What web service ?
Christian Graus
No longer a Microsoft MVP, but still happy to answer your questions.
|
|
|
|
|
Christian Graus wrote: What web service ?
Beats me. I didn't get the impression the OP already created a web service 
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
if you can give ip, username and password to the sql server then
why not directly connect to the database and insert or retireve records.
If i am getting your point right then if its a desktop application then direct connection won't do any harm to the database.
|
|
|
|
|
hi.. could you pls help me solve this problem..i am just finding the way "how to import .csv or .txt file into listview control??" i have a code which is akin to it but it reads everything inside the file add to the listview only one line.. so how about line by line adding??... below is my code..
Private Function ReadTextFile(ByVal sFileName As String) As String
Dim s As String
Try
Dim oFile As FileStream = New FileStream(sFileName, FileMode.Open, FileAccess.Read, FileShare.Read)
Dim oReader As StreamReader = New StreamReader(oFile)
s = oReader.ReadToEnd()
oReader.Close()
oFile.Close()
ReadTextFile = s
Catch
ReadTextFile = "Unable to open file."
End Try
End Function
Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim s As String
s = ReadTextFile(Environment.CurrentDirectory & "\ReadMe.txt")
ListView1.Items.Add(s)
'MessageBox.Show(s)
End Sub
|
|
|
|
|
File.ReadAllLines returns an array of strings which is a valid data source for a list view.
Christian Graus
No longer a Microsoft MVP, but still happy to answer your questions.
|
|
|
|
|
Learn something new every day, this is why I lurk here, thanks.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Yeah thanks 
I was always using a more complicated way of reading lines into an array 
Lloyd J. Atkinson
"Logic will get you from A to B, but imagination will take you everywhere" - ALbert Einstein
I look at Microsoft, and turn to my poster on the wall saying: "Bang head here in case of stress".
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
