| Author |
 Topic Search  Topic Options
|
hack_it 
Newbie
Joined: 16 November 2003
Status: Offline
Points: 9
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Zero Fields in Database
Posted: 10 December 2003 at 9:51am |
Im trying to read a .csv file in my database fields using the split() function. It seems to be splitting the line into the array perfectly but its when i come to the Sql that its letting me down.
It is accessing the DB but its entering zeroes for the fields. I know its going through the Do while loop correctly as it makes the correct number of records in the DB but with zero values in all.
Heres a snippet of my code, i've included the Sgl query, and the do while loop
aString = "Insert INTO Grades ([StudentID], [ModuleID], [AssignmentNumber], [Mark]) VALUES ('"& U_ID &"', '"& M_ID &"', '"& A_NUM &"', '"& Mrk &"')"
Do While objStreamReader.Peek() <> -1
Dim contents As String = objStreamReader.ReadLine()
Dim textdelimiter as String = ","
Dim splitout = Split(contents,textdelimiter)
U_ID = splitout(0)
M_ID = splitout(1)
A_Num = splitout(2)
Mrk = splitout(3)
myCommand = New OleDbCommand(aString, myConnection)
myCommand.ExecuteNonQuery()
Loop
Has anyone encountered this problem before?? I'm really stuck on it and need help fast.
|
 |
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 December 2003 at 10:04am |
That seems a pretty dodgy way of reading CSV's. I would use ADO.net to load it into a dataset http://authors.aspalliance.com/ericm/articles/textdb.asp
|
|
hack_it
Newbie
Joined: 16 November 2003
Status: Offline
Points: 9
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 December 2003 at 10:11am |
Ok Mart,
I've had a read of that page, am i right in thinking that i can do someting more to move the data into a relevant table in my database from the dataset??? If so is it the best way to read from a .csv and how is it best achieved?
|
|
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 December 2003 at 1:59pm |
I don't quite know what you mean... If you want to load a database into a data set look at this code
Dim objIdbConn As IDbConnection = New SqlConnection("server=(local);uid=xxx;pwd=xxx;database=xxx")
Dim objCommand As IDbCommand = New SqlCommand
Dim objDataAdapter As IDbDataAdapter = New SqlDataAdapter
Dim objDataSet As New DataSet
objCommand.CommandText = "exec Query;"
objCommand.Connection = objIdbConn
objDataAdapter.SelectCommand = objCommand
objDataAdapter.Fill(objDataSet)
DataGrid1.DataSource = objDataSet
DataGrid1.DataBind()
|
|
|
Mart
Senior Member
Joined: 30 November 2002
Status: Offline
Points: 2304
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 December 2003 at 2:01pm |
|
You can do similar with microsoft access by using the System.Data.OleDb namespace instead...
|
|
hack_it
Newbie
Joined: 16 November 2003
Status: Offline
Points: 9
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 December 2003 at 3:22pm |
Mart
I have this process working now.
Im having a problem with one of my other pages. When a user clicks on the submit button it is suppose to change a field relating to that user in the DB and email them the resultant change. However, when i click the button, the email seems to be sent before the change is made and as a result they are sent the old data instead of the new. Have you any idea what cuases such an error?
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
Posted: 10 December 2003 at 3:31pm |
Why so complicated. Let SQL do the job
Dim SQLCon As New SqlClient.SqlConnection("server=(local);uid=zzz;pwd=rrr;data base=test")
Dim myQuery As New SqlClient.SqlCommand
myQuery.CommandText = "BULK INSERT tblImport FROM 'c:\trnexp.csv' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')"
myQuery.Connection = SQLCon
SQLCon.Open()
myQuery.ExecuteNonQuery()
SQLCon.Close()
|
|
|
|
|
