-
Dec 24th, 2010, 12:01 PM
#41
Re: Retrieving and Saving Data in Databases
In which case, go back and read jmcilhinney's post, #36. It tells you exactly what you need to do.
Download MySql Connector for .Net.
Reference the assembly in your project.
Update the Imports statements at the top of your code file.
Change all SqlConnection to MySqlConnection, all SqlCommand to MySqlCommand etc.
Update your code to use Parameters.
Gary
-
Dec 24th, 2010, 12:02 PM
#42
Re: Retrieving and Saving Data in Databases
Originally Posted by Dsnowdon
I dont kn
Hold on. Are you saying you are, or aren't using MySql? Your posts seems contradictory?!?!
Gary
-
Dec 24th, 2010, 12:11 PM
#43
Junior Member
Re: Retrieving and Saving Data in Databases
No i am uing MYSQL and im using
Imports MySql.Data.MySqlClient
Imports System.Data.SqlClient
at the top of my code
I need help with this statment i have changed my code and i get the error on
vb Code:
Online = CDbl(command.ExecuteScalar())
This is the whole code
vb Code:
Dim MySQLConnection As MySqlConnection MySqlConnection = New MySqlConnection MySqlConnection.ConnectionString = "server=dddd.net;Port=3306; User ID=dddddd; password=ddddd; database=dddddd" MySqlConnection.Open() Using command As New SqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';") Online = CDbl(command.ExecuteScalar()) End Using
Sorry to be a pain
Thanks dale
-
Dec 24th, 2010, 12:14 PM
#44
Re: Retrieving and Saving Data in Databases
Hey,
Since you are using MySql, you will not need this:
Code:
Imports System.Data.SqlClient
Okay, you are going to have to help us here...
What is the error that you are getting?
Gary
-
Dec 24th, 2010, 12:15 PM
#45
Re: Retrieving and Saving Data in Databases
I am going to guess that is has to do with this:
Code:
Using command As New SqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';")
You are still using SqlCommand.
Also, you haven't still not using Parameters. I HIGHLY recommend that you start using them. There is a link in my signature explaining why you should use them.
Gary
-
Dec 24th, 2010, 12:19 PM
#46
Junior Member
Re: Retrieving and Saving Data in Databases
OK so what should i change SQLCommand to?
-
Dec 24th, 2010, 12:21 PM
#47
Re: Retrieving and Saving Data in Databases
Originally Posted by Dsnowdon
OK so what should i change SQLCommand to?
Did you read post #41?
-
Dec 24th, 2010, 12:29 PM
#48
Junior Member
Re: Retrieving and Saving Data in Databases
OK i have changed that but when it runs it still get a error with
The Error is
Code:
System.InvalidOperationException was unhandled
Message=Connection must be valid and open.
Source=MySql.Data
-
Dec 24th, 2010, 12:31 PM
#49
Junior Member
Re: Retrieving and Saving Data in Databases
It is connected becaue i have or the user has already loged in before this code is ran
-
Dec 24th, 2010, 12:36 PM
#50
Re: Retrieving and Saving Data in Databases
Hey,
Ok, a slight suggestion, using MySqlConnection as your variable name is a poor choice, since this is the name of the class that you are instantiating.
Try this:
Code:
Using connection As New MySqlConnection("server=dddd.net;Port=3306; User ID=dddddd; password=ddddd; database=dddddd")
Using command As New MySqlCommand("SELECT * online FROM users WERE username='" & LoginForm1.UsernameTextBox.Text & "' AND password = '" & LoginForm1.PasswordTextBox.Text & "';", connection)
command.CommandType = CommandType.Text
connection.Open()
Online = CDbl(command.ExecuteScalar())
End Using
End Using
Notice that I have passed the MySqlConnection object as the second parameter to the constructor for the MySqlCommand object.
P.S. Bear in mind that the above was written outside of Visual Studio, and I can't guarantee that it is 100% correct.
Gary
-
Dec 24th, 2010, 12:41 PM
#51
Junior Member
Re: Retrieving and Saving Data in Databases
I think that worked but now i have another
sorry about this its the first timei have used MySQl qith vb.net
The error i have it
Code:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'online FROM users WERE username='dale' AND password = '*******'' at line 1
-
Dec 24th, 2010, 12:47 PM
#52
Re: Retrieving and Saving Data in Databases
Hey,
This is basically an error with your actual SQL query. You need to first make sure that the query works directly against the database, before trying to run it through your code. Do you have the MySql Tools installed, so that you can run the query in Query Browser? If not, I would highly recommend that you do this. Make sure you can run the query, and get the result you expect, before trying to run your code.
Also, this would be a good opportunity to change you MySqlCommand object to use Parameters.
I am away to sign off now, but I will try and check back in the next couple days to see how you are getting on.
As a quick clue though, I think this is going to be part of your problem:
Gary
-
Mar 9th, 2011, 10:33 AM
#53
New Member
Re: Retrieving and Saving Data in Databases
Originally Posted by dethredic
I am having some problems:
vb Code:
Using connection As New OleDbConnection(myConnectionString)
Using adapter As New OleDbDataAdapter("SELECT NameFirst, NameLast FROM Employees", _
connection)
Dim insert As New OleDbCommand("INSERT INTO Employees (NameFirst, NameLast) VALUES (@NameFirst, @NameLast)", _
connection)
adapter.InsertCommand = insert
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim table As New DataTable
adapter.FillSchema(table, SchemaType.Source)
Dim row As DataRow = table.NewRow()
row("NameFirst") = aName(0)
row("NameLast") = aName(1)
table.Rows.Add(row)
adapter.Update(table)
End Using
End Using
I am getting an error with this line:
adapter.Update(table)
error:
No value given for one or more required parameters.
Hi,
I'm using DotNetNuke CMS and working on some modules using VB.Net. I have a Datagrid "telerik:RadGrid" and I'm trying to add textbox to filter the rows base on the value that is written in the text box:
I defined the following:
Dim dv As DataView = New DataView()
dv.Table = ?
dv.RowFilter = "Name like '" & TextBox2.Text & "%'"
RadGrid1.DataSource = dv
I have a problem in setting the value of the dv.Table=?
Can you please guide me to the value that should be there
Thanks
-
Apr 30th, 2011, 11:09 AM
#54
Fanatic Member
Re: Retrieving and Saving Data in Databases
hi all expert, i need help to fill in textbox1 and textbox2 with sendernumber and textdecoded, but when run, both textbox is fill-n with last record(not from the beginning record)
what command should i do? here is the code is working with messagebox, thanks !
Code:
populate textbox from database
'this working
Private Sub cmdLogin_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdLogin.Click
Using conn As New MySqlConnection("server=" & txtServer.Text & ";" _
& "user id=" & txtUsername.Text & ";" _
& "password=" & txtPassword.Text & ";" _
& "database=smsd_telkom")
Using command As New MySqlCommand("SELECT sendernumber,textdecoded FROM inbox", _
conn)
conn.Open()
Using reader As MySqlDataReader = command.ExecuteReader()
While reader.Read()
'TextBox10.Text = reader("sendernumber")
'TextBox11.Text = reader("textdecoded")
MessageBox.Show(String.Format("There are {0} {1}in stock.", _
reader("sendernumber"), _
reader("textdecoded")))
End While
End Using
End Using
End Using
end sub
Last edited by ksuwanto8ksd; Apr 30th, 2011 at 11:13 AM.
-
Apr 30th, 2011, 10:06 PM
#55
Re: Retrieving and Saving Data in Databases
Originally Posted by ksuwanto8ksd
hi all expert, i need help to fill in textbox1 and textbox2 with sendernumber and textdecoded, but when run, both textbox is fill-n with last record(not from the beginning record)
what command should i do? here is the code is working with messagebox, thanks !
It's displaying the data from the last record because you are using a loop. Every iteration of the loop, what's currently in the TextBoxes is replaced with the data from the current record. The only data that doesn't get replaced is the data from the last row, so that's what you see.
If you only want data from the first row then why are you using a loop at all. Loops are for doing something multiple times. If you only want to do something once then why would you use a loop? Presumably because you have copied and pasted code without considering what it actually does. I would question why you're executing a query that can return multiple records at all if all you want is one record.
Anyway, if all you want is to read one record then first test the HasRows property to make sure that there is at least one row present, then call Read once and once only, to read just one row.
-
Jun 4th, 2011, 10:05 AM
#56
Re: Retrieving and Saving Data in Databases
i followed your advice + followed the link to this thread, hoping to find how to update 2 tables using 2 adapters + 2 datatables, but there's nothing here that answers how to keep both tables synchronized.
here's my code... can you help?
vb Code:
Public Class dataLayer
Private Shared connection As OleDb.OleDbConnection
Private Shared updatableAdapter(1) As OleDb.OleDbDataAdapter
Private Shared transaction As OleDb.OleDbTransaction
Private Shared Sub connect()
connection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & IO.Path.Combine(My.Application.Info.DirectoryPath, "details.accdb") & ";Persist Security Info=False;")
connection.Open()
End Sub
Private Shared Sub disConnect()
connection.Close()
End Sub
Public Shared Function getJoinedTable() As DataTable
connect()
updatableAdapter(0) = New OleDb.OleDbDataAdapter("SELECT a.id, a.number, a.subjectName, a.years, a.birthYear, b.note FROM Table1 AS a INNER JOIN Table2 AS b ON a.id = b.id", connection)
updatableAdapter(1) = New OleDb.OleDbDataAdapter("SELECT * FROM Table2", connection) 'contains id, note
' Create the InsertCommand.
Dim command As New OleDb.OleDbCommand("INSERT INTO Table1 VALUES(@id, @number, @subjectName, @years, @birthYear)", connection)
' Add the parameters for the InsertCommand.
command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
command.Parameters.Add("@number", OleDb.OleDbType.VarChar, 50, "number")
command.Parameters.Add("@subjectName", OleDb.OleDbType.VarChar, 50, "subjectName")
command.Parameters.Add("@years", OleDb.OleDbType.Integer, 3, "years")
command.Parameters.Add("@birthYear", OleDb.OleDbType.Integer, 4, "birthYear")
'command.Parameters.Add("@note", OleDb.OleDbType.VarChar, 100, "note")
updatableAdapter(0).InsertCommand = command
command = New OleDb.OleDbCommand("INSERT INTO Table2 VALUES(@id, @note)", connection)
' Add the parameters for the InsertCommand.
command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
command.Parameters.Add("@note", OleDb.OleDbType.VarChar, 100, "note")
updatableAdapter(1).InsertCommand = command
' Create the UpdateCommand.
command = New OleDb.OleDbCommand( _
"UPDATE Table1 SET id = @id, [number] = @number, subjectName = @subjectName, years = @years, " & _
"birthYear = @birthYear WHERE id = @oldID", connection)
' Add the parameters for the UpdateCommand.
command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
command.Parameters.Add("@number", OleDb.OleDbType.VarChar, 50, "number")
command.Parameters.Add("@subjectName", OleDb.OleDbType.VarChar, 50, "subjectName")
command.Parameters.Add("@years", OleDb.OleDbType.Integer, 3, "years")
command.Parameters.Add("@birthYear", OleDb.OleDbType.Integer, 4, "birthYear")
Dim parameter As OleDb.OleDbParameter = command.Parameters.Add( _
"@oldID", OleDb.OleDbType.Integer, 5, "id")
parameter.SourceVersion = DataRowVersion.Original
updatableAdapter(0).UpdateCommand = command
' Create the UpdateCommand.
command = New OleDb.OleDbCommand( _
"UPDATE Table2 SET id = @idnote = @note WHERE id = @oldID", connection)
' Add the parameters for the UpdateCommand.
command.Parameters.Add("@id", OleDb.OleDbType.Integer, 5, "id")
command.Parameters.Add("@note", OleDb.OleDbType.VarChar, 100, "note")
parameter = command.Parameters.Add( _
"@oldID", OleDb.OleDbType.Integer, 5, "id")
parameter.SourceVersion = DataRowVersion.Original
updatableAdapter(1).UpdateCommand = command
' Create the DeleteCommand.
command = New OleDb.OleDbCommand( _
"DELETE FROM Table1 WHERE id = @id", connection)
' Add the parameters for the DeleteCommand.
parameter = command.Parameters.Add( _
"@id", OleDb.OleDbType.Integer, 5, "id")
parameter.SourceVersion = DataRowVersion.Original
updatableAdapter(0).DeleteCommand = command
' Create the DeleteCommand.
command = New OleDb.OleDbCommand( _
"DELETE FROM Table2 WHERE id = @id", connection)
' Add the parameters for the DeleteCommand.
parameter = command.Parameters.Add( _
"@id", OleDb.OleDbType.Integer, 5, "id")
parameter.SourceVersion = DataRowVersion.Original
updatableAdapter(1).DeleteCommand = command
Dim dt As New DataTable
updatableAdapter(0).Fill(dt)
Return dt
End Function
Public Shared Sub update(ByVal dt As DataTable, ByVal adapterIndex As Integer)
updatableAdapter(adapterIndex).Update(dt)
connection.Close()
End Sub
End Class
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 4th, 2011, 10:58 AM
#57
Re: Retrieving and Saving Data in Databases
Originally Posted by .paul.
i followed your advice + followed the link to this thread, hoping to find how to update 2 tables using 2 adapters + 2 datatables, but there's nothing here that answers how to keep both tables synchronized.
here's my code... can you help?
I'm not sure I understand. Do you actually mean two database tables, two DataAdapters and ONE DataTable? If it's 2, 2 and 2 then that's just 1, 1 and 1 twice, so it's no different to what you normally do.
-
Jun 4th, 2011, 11:07 AM
#58
Re: Retrieving and Saving Data in Databases
it's 2 database tables, two DataAdapters and (should be) 2 DataTables
the 1st dataadapter queries both tables + fills the 1st datatable which i then bind to a dgv
the 2nd dataadapter should fill a 2nd datatable with just 2 fields (the id field is the primarykey in the 1st database table + the foreign key in the 2nd database table. the 2 fields are id, + note)
the problem is, changing values in the dgv alters the 1st datatable but the 2nd datatable remains the same. i need to synchronize the 2 datatables so when i update the 2nd dataadapter, it has the latest values
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 4th, 2011, 11:21 AM
#59
Re: Retrieving and Saving Data in Databases
Originally Posted by .paul.
it's 2 database tables, two DataAdapters and (should be) 2 DataTables
the 1st dataadapter queries both tables + fills the 1st datatable which i then bind to a dgv
the 2nd dataadapter should fill a 2nd datatable with just 2 fields (the id field is the primarykey in the 1st database table + the foreign key in the 2nd database table. the 2 fields are id, + note)
the problem is, changing values in the dgv alters the 1st datatable but the 2nd datatable remains the same. i need to synchronize the 2 datatables so when i update the 2nd dataadapter, it has the latest values
I don't think there's any way to do that other than to handle events of the first DataTable and update the second DataTable manually.
-
Jun 4th, 2011, 11:26 AM
#60
Re: Retrieving and Saving Data in Databases
i asked on MSDN + i was told that i could synchronize 2 datatables with an oledbTransaction but i can't find a clear example of how to do that
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 4th, 2011, 12:15 PM
#61
Re: Retrieving and Saving Data in Databases
Originally Posted by .paul.
i asked on MSDN + i was told that i could synchronize 2 datatables with an oledbTransaction but i can't find a clear example of how to do that
I don't know who told you that but they must not have understood the question. What a transaction will allow you to do is save the two DataTables to the database and ensure that they either both succeed or both fail.
I still don't see the need for the second DataTable. If you're making changes to the first DataTable then why don't you just save the changes from the first DataTable to both database tables? By default, calling Update on one DataAdapter would implicitly call AcceptChanges on the DataTable, so there'd be nothing left to save with the second. For that reason, you just have to set AcceptChangesDuringUpdate to False so that the changes are maintained for the second DataAdapter.
-
Jun 5th, 2011, 12:57 PM
#62
Re: Retrieving and Saving Data in Databases
Originally Posted by jmcilhinney
I don't know who told you that but they must not have understood the question. What a transaction will allow you to do is save the two DataTables to the database and ensure that they either both succeed or both fail.
I still don't see the need for the second DataTable. If you're making changes to the first DataTable then why don't you just save the changes from the first DataTable to both database tables? By default, calling Update on one DataAdapter would implicitly call AcceptChanges on the DataTable, so there'd be nothing left to save with the second. For that reason, you just have to set AcceptChangesDuringUpdate to False so that the changes are maintained for the second DataAdapter.
that appears to be the answer but i was + still am struggling with it because using 1 datatable with 2 dataadapters works fine for update + delete, but insert will only insert into the 1st database table. it's as if the AcceptChangesDuringUpdate is ignored for the 1st insert command
edit:that was happening... calling Update on the 1st DataAdapter was calling AcceptChanges on the DataTable
the solution is:
vb Code:
Dim tempDt As DataTable = dt.GetChanges
DataAdapter1.update(dt)
If tempDt IsNot Nothing Then
DataAdapter2.update(tempDt)
End If
thanks for the help
Last edited by .paul.; Jun 5th, 2011 at 02:23 PM.
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 7th, 2011, 11:32 AM
#63
Fanatic Member
Re: Retrieving and Saving Data in Databases
Hello jmc
if i need to batch insert into database with transaction support, so that i can roll back, how do i do it?
-
Jun 7th, 2011, 06:46 PM
#64
Re: Retrieving and Saving Data in Databases
Originally Posted by coolcurrent4u
Hello jmc
if i need to batch insert into database with transaction support, so that i can roll back, how do i do it?
A DataAdapter does that inherently. A single call to Update will either succeed entirely or fail entirely. If you want to call Update on multiple DataAdapters and/or call ExecuteNonQuery on multiple Commands, then you would want to use a transaction explicitly. In that case, you have two choices:
1. Use the transaction class from your ADO.NET provider, e.g.
vb.net Code:
Using connection As New SqlConnection("connection string here")
connection.Open()
Dim transaction As SqlTransaction = connection.BeginTransaction()
Using command1 As New SqlCommand("INSERT statement here", connection) With {.Transaction = transaction},
command2 As New SqlCommand("INSERT statement here", connection) With {.Transaction = transaction},
adapter1 As New SqlDataAdapter With {.InsertCommand = command1},
adapter2 As New SqlDataAdapter With {.InsertCommand = command2}
'Add appropriate parameters command1 and command2 here.
Try
adapter1.Update(table1)
adapter2.Update(table2)
'All operations succeeded so commit.
transaction.Commit()
Catch ex As Exception
'At least one operation failed so rollback.
transaction.Rollback()
End Try
End Using
End Using
2. Use a TransactionScope object, e.g.
vb.net Code:
Using transaction As New TransactionScope
Using connection As New SqlConnection("connection string here")
connection.Open()
Using command1 As New SqlCommand("INSERT statement here", connection),
command2 As New SqlCommand("INSERT statement here", connection),
adapter1 As New SqlDataAdapter With {.InsertCommand = command1},
adapter2 As New SqlDataAdapter With {.InsertCommand = command2}
'Add appropriate parameters command1 and command2 here.
Try
adapter1.Update(table1)
adapter2.Update(table2)
'All operations succeeded so commit.
transaction.Complete()
Catch ex As Exception
'At least one operation failed.
'There is no need to rollback explicitly as it will be done implicitly at the End of
'the Using block that created the TransactionScope if Complete was not called.
End Try
End Using
End Using
End Using
-
Jun 8th, 2011, 11:19 AM
#65
New Member
Re: Retrieving and Saving Data in Databases
Thanks for posting the connection methods, they have been a great help. However I have run into a issue.
vb Code:
Try
Using Command As New OleDbCommand("INSERT INTO USERTABLE " _
+ "(UserInitials, Ulastname, UFirstName, UMidInit, UPassw, UsecLevel," _
+ " UDept, Userstatus, UValid,UPhone, SuperID, UserName) VALUES " _
+ "(@UserInit,@ULastName,@UFirstName,@UMidInit,@UPass, " _
+ " @UsecLvl,@UDept,@UStatus,@UValid,@UPhone,@SuperID, @UserName)", cnn)
'+ "('" + strFirstName.Substring(0, 1) + strMiddle + strLastName.Substring(0, 1) _
'+ "','" + strLastName + "','" + strFirstName + "','" + strMiddle + "','" _
'+ strPass + "','" + strSecLvl + "','" + strDept + "','" + strUserStatus + _
'"','" + strValid + "','" + strPhone + "','" + strSuper + "','" + strUserName + "')", cnn)
Command.Parameters.AddWithValue("@UserInit", strFirstName.Substring(1, 1) + strMiddle + strFirstName.Substring(1, 1))
Command.Parameters.AddWithValue("@ULastName", strLastName)
Command.Parameters.AddWithValue("@UFirstName", strFirstName)
Command.Parameters.AddWithValue("@UMidInit", strMiddle)
Command.Parameters.AddWithValue("@UsecLvl", strSecLvl)
Command.Parameters.AddWithValue("@UPass", strPass)
Command.Parameters.AddWithValue("@Dept", strDept)
Command.Parameters.AddWithValue("@UStatus", strUserStatus)
Command.Parameters.AddWithValue("@UValid", strValid)
Command.Parameters.AddWithValue("@Uphone", strPhone)
Command.Parameters.AddWithValue("@USuperid", strSuper)
Command.Parameters.AddWithValue("@UserName", strUserName)
cnn.Open()
Command.ExecuteNonQuery()
End Using
cnn.Close()
fAddUSer = True
Catch ex As Exception
MsgBox("Couldnt connect.")
cnn.Close()
fAddUSer = False
End Try
When I use the code with the commented out string it works, but when I try to use it with the parameters, I end up with and error message ORA-00936: missing expression. I obiously have something wrong, but since it works with the commented out section, I am a bit stumped.
Any help is greatly appreciated.
-
Jun 8th, 2011, 02:23 PM
#66
Re: Retrieving and Saving Data in Databases
try listing your AddWithValue statements in the order you use them in the INSERT statement
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 8th, 2011, 02:26 PM
#67
Re: Retrieving and Saving Data in Databases
also have you noticed your commented out string has 13 values + your parameters only 12?
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 8th, 2011, 02:57 PM
#68
New Member
Re: Retrieving and Saving Data in Databases
Thanks for trying to help. I did move them in order and also made sure they all have the right name @..... but I still get the same error.
In regards to the 13 value vs 12, I only found 12, you may have counted the first line as 2 values as I'm combining the last, first and middle into the initials or the person.
Also I'm connecting to Oracle db, and I know they are picking in regards to qhoutatins for strings etc... so that 'Super' would work, while just Super would not... I don't know if the parameters automatically does that or not.
Thanks
Last edited by BLUMONKEY; Jun 8th, 2011 at 03:04 PM.
-
Jun 8th, 2011, 06:42 PM
#69
Re: Retrieving and Saving Data in Databases
@BLUMONKEY:
Oracle doesn't use '@' as a parameter prefix. You need to use ':' as a parameter prefix for Oracle.
Also, rather than using OleDb, I would suggest installing the Oracle-specific ADO.NET provider from Oracle.
-
Jun 9th, 2011, 01:02 AM
#70
Re: Retrieving and Saving Data in Databases
As a starting point for John's recommendation to use ODP.Net, you might want to take a look here:
http://www.stanford.edu/dept/itss/do...7/intro003.htm
Specifically:
http://www.stanford.edu/dept/itss/do...2.htm#i1010814
Gary
-
Jun 9th, 2011, 04:18 AM
#71
Fanatic Member
Re: Retrieving and Saving Data in Databases
@Jmc thanks see the code am using already, is there any issue in operformance with it, am using sqlite.net
vb Code:
Dim conn As New SQLiteConnection("Data Source=" & dbPath)
'conn.SetPassword(dbPassword)
conn.Open()
Dim cmd As New SQLiteCommand(conn)
Try
mytransaction = conn.BeginTransaction()
'// insert directory //
For i = 0 To UBound(oDir)
If oDir(i).Url IsNot Nothing And DirExist(oDir(i).Url) = False Then
cmd = New SQLiteCommand("INSERT INTO directory ([name]," & _
"[category],[url],[type]) VALUES (@name,@category,@url,@type)")
cmd.Parameters.AddWithValue("@name", oDir(i).Name.ToLower)
cmd.Parameters.AddWithValue("@category", oDir(i).Category.ToLower)
cmd.Parameters.AddWithValue("@type", oDir(i).Type.ToLower)
cmd.Parameters.AddWithValue("@url", oDir(i).Url.ToLower)
'Debug.Print(cmd.CommandText)
cmd.ExecuteNonQuery()
With tmpDir
.Category = oDir(i).Category.ToLower
.Name = oDir(i).Name.ToLower
.Type = oDir(i).Type.ToLower
.Url = oDir(i).Url.ToLower
End With
lstDirs.Add(tmpDir)
End If
Next
mytransaction.Commit()
ListDirectories.AddRange(lstDirs)
Return True
Catch ex As Exception
strLastError = ex.Message
Debug.Print(strLastError)
Return False
Finally
cmd.Dispose()
conn.Dispose()
End Try
-
Jun 9th, 2011, 08:30 AM
#72
New Member
Re: Retrieving and Saving Data in Databases
Originally Posted by jmcilhinney
@BLUMONKEY:
Oracle doesn't use '@' as a parameter prefix. You need to use ':' as a parameter prefix for Oracle.
Also, rather than using OleDb, I would suggest installing the Oracle-specific ADO.NET provider from Oracle.
Thank you! that did the trick.
In regards to the Oracle Specific ADO.NET (this may be a newbie question too) would that have to be installed on every machine that will be using this app? The reason I am asking is that technology may have to approve something like that, while the oledb is already on the machines.
Thanks again!
Last edited by BLUMONKEY; Jun 9th, 2011 at 08:32 AM.
Reason: Added some.
-
Jun 9th, 2011, 09:09 AM
#73
Re: Retrieving and Saving Data in Databases
Originally Posted by BLUMONKEY
Thank you! that did the trick.
In regards to the Oracle Specific ADO.NET (this may be a newbie question too) would that have to be installed on every machine that will be using this app? The reason I am asking is that technology may have to approve something like that, while the oledb is already on the machines.
Thanks again!
You can install the provider on each machine, or you can just deploy the required DLLs with your EXE.
-
Jun 9th, 2011, 09:40 AM
#74
New Member
Re: Retrieving and Saving Data in Databases
Thank you again!
If you have the time... what is the biggest difference between oledb and the oracle driver? Will there be a performance gain?
Thank you for all the help you have given me!
Ivar
-
Jun 9th, 2011, 06:37 PM
#75
Re: Retrieving and Saving Data in Databases
Originally Posted by BLUMONKEY
Thank you again!
If you have the time... what is the biggest difference between oledb and the oracle driver? Will there be a performance gain?
Thank you for all the help you have given me!
Ivar
That is well beyond the scope of this CodeBank thread. Please keep discussion in the CodeBank specifically to the topic of that thread. If you have questions on other topics then you can start a new thread in the appropriate form. Otherwise CodeBank threads get cluttered and less useful.
-
Jun 10th, 2011, 08:19 AM
#76
New Member
Re: Retrieving and Saving Data in Databases
No problem... thanks again.
-
Nov 24th, 2011, 09:37 AM
#77
Member
Re: Retrieving and Saving Data in Databases
JMC you are a Champion............
-
Nov 24th, 2011, 09:39 AM
#78
Member
Re: Retrieving and Saving Data in Databases
hello JMC sir can you update this codes with LinQ and LambdA ...
.sorry for this asking because i'm trying to learn this stuffs
thanks........
-
Jan 16th, 2012, 04:13 PM
#79
Junior Member
Re: Retrieving and Saving Data in Databases
Last edited by Jack Tomy; Jan 16th, 2012 at 04:40 PM.
-
Jan 16th, 2012, 04:39 PM
#80
Junior Member
Re: Retrieving and Saving Data in Databases
worked perfect, thank you
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|