|
I am guessing that you have a loop outside of the code you posted, so I would do something like this:
Dim myConnString As String = My.Settings.ConnectionString
Dim MyConnection As New Odbc.OdbcConnection(myConnString)
Try
MyConnection.Open()
'your loop code here
Dim MyCommand As New Odbc.OdbcCommand("INSERT INTO ls_orderitems (OrderDate, OrderCustomer, OrderAcctNum, OrderType, OrderSoNum, OrderItemPartNum, OrderItemPartDesc, OrderItemQty, OrderItemQtyRec, OrderItemQtyDtRec, OrderItemBO, OrderItemBODt,OrderItemCanceled, OrderItemCanceledDt, OrderItemNotes, OrderItemPONum, OrderItemRetailPrice, OrderItemSalePrice, Status, InsertDate) VALUES('" & OrderDate & "','" & OrderCustomer & "','" & OrderAcctNum & "','" & OrderType & "','" & OrderSoNum & "','" & OrderItemPartNum & "','" & OrderItemPartDesc & "','" & OrderItemQty & "','" & OrderItemQtyRec & "','" & OrderItemQtyDtRec & "','" & OrderItemBO & "','" & OrderItemBODt & "','" & OrderItemCanceled & "','" & OrderItemCanceledDt & "','" & OrderItemNotes & "','" & OrderItemPONum & "','" & OrderItemRetailPrice & "','" & OrderItemSalePrice & "','" & Status & "','" & InsertDate & "')")
MyCommand.Connection = MyConnection
MyCommand.ExecuteNonQuery()
'bottom of your loop code
Finally
MyConnection.Close()
End Try
That way you only open the connection once then do all your inserts, finally close the connection.
Hope that helps.
Ben
|
|
|
|
|
But what if I have the loop code and the insert statement in two different functions?
|
|
|
|
|
Pass in the connection object into the function that does the insert.
So you have:
outter function
Dim myConnString As String = My.Settings.ConnectionString
Dim MyConnection As New Odbc.OdbcConnection(myConnString)
Try
MyConnection.Open()
'loop goes here
'Call inner function pass in MyConnection
'bottom of loop
Finally
MyConnection.Close()
End Try
You innner function looks like:
Private Sub InnerFunc(p_MyConnection As Odbc.OdbcConnection)
Dim MyCommand As New Odbc.OdbcCommand("INSERT INTO ls_orderitems (OrderDate, OrderCustomer, OrderAcctNum, OrderType, OrderSoNum, OrderItemPartNum, OrderItemPartDesc, OrderItemQty, OrderItemQtyRec, OrderItemQtyDtRec, OrderItemBO, OrderItemBODt,OrderItemCanceled, OrderItemCanceledDt, OrderItemNotes, OrderItemPONum, OrderItemRetailPrice, OrderItemSalePrice, Status, InsertDate) VALUES('" & OrderDate & "','" & OrderCustomer & "','" & OrderAcctNum & "','" & OrderType & "','" & OrderSoNum & "','" & OrderItemPartNum & "','" & OrderItemPartDesc & "','" & OrderItemQty & "','" & OrderItemQtyRec & "','" & OrderItemQtyDtRec & "','" & OrderItemBO & "','" & OrderItemBODt & "','" & OrderItemCanceled & "','" & OrderItemCanceledDt & "','" & OrderItemNotes & "','" & OrderItemPONum & "','" & OrderItemRetailPrice & "','" & OrderItemSalePrice & "','" & Status & "','" & InsertDate & "')")
MyCommand.Connection = p_MyConnection
MyCommand.ExecuteNonQuery()
End Sub
Hope that helps.
Ben
|
|
|
|
|
Question, when I run the program on my computer the files will insert pretty quick. But when I run it on the computer that I installed the program on, I run into some problems. After the program run for about 1 minute or so an error would occur that says cannot connect to MySQL server. However, when I disable windows firewall the program will run without throwing the error but the data inserts slow. It takes about 20 min for the first file to insert on my computer, while on the other it took about an hour. Any ideas why it works fine on my computer and not the other? Is disabling the firewall making it run slow? It's very confusing.
|
|
|
|
|
If you have a local MySql database on your local computer, that will always run faster then over the network. It also may help if your local table is empty or doesn't have many records.
I am not sure why disabling the windows firewall causes the program to run. I am guessing for some reason your firewall is preventing access to the sql server.
So on the other computer, if it is going across the network it will be slower then your local PC writings to a local database. It also might be an issue if you already have lots of records on the other MySQL database the inserts can take a longer time. This gets into my suggestion of dropping indexes and some things like that.
Hope that helps.
Ben
|
|
|
|
|
If I install MySQL locally on the computer which I installed the program, will the data import faster?
|
|
|
|
|
Yes if you are inserting into the local MySql database. Inserts will always be faster if you don't have to go across the network. If you are still inserting across the network the installing MySql will not help the speed. What helps the speed is that the program and the database it is inserting into are on the same computer.
Ben
|
|
|
|
|
It sounds like you're using the ODBC driver layer to communicate with MySQL. You may get better performance with Connector/Net[^], the native .NET data provider for MySQL.
Also, consider preparing the statement - create the command object once, and call Prepare on it. This means that the server only has to parse the query once, not repeatedly. Don't forget to Dispose the command object once you've finished with it.
|
|
|
|
|
Since this Connector/Net is fairly new to me, I don't know how to change my code to reflect this. Do you think you could provide me with some help, any kind of samples will help.
Thanks
|
|
|
|
|
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user 'IN_FORMO'. Reason: Not associated with a trusted SQL Server connection.
How can i solve this
I read BOL, try all what can find,
Is there something about Windows Accounts or where...else
|
|
|
|
|
1-post the connection string you use
2-do you use a domain?
3-is the application user added to sql server logins?
|
|
|
|
|
i have done it, it was not sush big proplem
problem was my admin, WAS because he is a**hole!!
|
|
|
|
|
IF @ProductID IS NULL OR @ProductID = '' <br />
BEGIN<br />
--Do nothing cos you suck balls<br />
SET @MainQuery = @MainQuery + '';<br />
END <br />
ELSE <br />
BEGIN<br />
SET @MainQuery = @MainQuery + ' AND L.ProductID = ''' + @ProductID + '''';<br />
END
I am using that code to add additional where clauses but I can't seem to work out how to do the If statement the other way around. Which would allow me to remove the Else chunk. I hope this makes sense as it is a simple issues. Thanks in advance
|
|
|
|
|
where
L.ProductID=
case coalesce(@productid,'') when '' then L.ProductID else @ProductID end
|
|
|
|
|
Ok could you explain exactly what this does. I don't understand how it performs the same task as my original code
|
|
|
|
|
If @param is null or '' then where will be:
...where l.product=l.porduct and will do nothing
but if @param has eny value where will be:
...where l.product=@param and will do filtering by column l.product
CASE..when...wnen...END is very powerfull and can be used in meny ways
|
|
|
|
|
OK thanks for that but im still not following. Perhaps this might help.
The code should add a filtering AND statement if the parameter was passed. As it stands I am checking for either a null value or no value to be passed and then doing the work in the else part of the statement . So really all I require is the code to check that a parameter has a value. If it does i want to perform the filter.
|
|
|
|
|
ok try this
IF @ProductID IS not NULL OR @ProductID <> ''
begin
SET @MainQuery = @MainQuery + ' AND L.ProductID = ''' + @ProductID + ''''
end
execute(@MainQuery)
|
|
|
|
|
I got the
@ProductID IS NOT NULL working fine.
The <> does't seem to work though. If I set ProductID to '' and use it I still get a succesfull match. Which of course not right?!
|
|
|
|
|
Change it to:
IF @ProductID IS NOT NULL AND @ProductID <> ''
The OR condition you were shown will evaluate to true if either part is true, which setting @ProductID to an empty string will do.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
That makes no sense. This will never evaluate to true ever. If @ProductID is null then it can't = ''. If @ProductID = '' it wont be null. Both side of this statement would have to be true for the whole thing to return true and this is not possible.
Unless of course SQL works in a completely different way to every other programming language I've used??
|
|
|
|
|
hi friend
what type is your @param
be fast
|
|
|
|
|
|
try
coalesce(@param,'') that mean if is null translate it like ''
|
|
|
|
|
Welcome to the wonderful world of NULLs. Try the following test:
DECLARE @ProductID NVARCHAR(10)
SET @ProductID = null
IF @ProductID IS NOT NULL AND @ProductID <> ''
BEGIN
PRINT 'Started 1st test'
END
ELSE
BEGIN
PRINT 'Failed 1st test'
END
-- Will print Failed 1st test
SET @ProductID = ''
IF @ProductID IS NOT NULL AND @ProductID <> ''
BEGIN
PRINT 'Started 2nd test'
END
ELSE
BEGIN
PRINT 'Failed 2nd test'
END
-- Will print Failed 2nd test
SET @ProductID = 'Hi'
IF @ProductID IS NOT NULL AND @ProductID <> ''
BEGIN
PRINT 'Started 3rd test'
END
ELSE
BEGIN
PRINT 'Failed 3rd test'
END
-- Will print Started 3rd test
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
