Insert xml into mssql 2000

Hi,

I have app with file selection field. Users can shoose xml file from their local hard disk and click Save. When they click save, content of xml file should be inserted into mssql 2000.

What is the most efficient way to do this using C#2005 and mssql2000?

Any good article or tutorial?

http://www.databasejournal.com/features/mssql/article.php/2204421|||http://aspalliance.com/477

insert then update...

greetings
I am developing an application for the marketing dept at my company.Basically users can build the content of an email to be sent to oursubscriber database.
I am wanting the application to initailly save the content into a database, the update the most recently inserted row.
The save button uses the following SQL command:
Dim SqlMethod As String ="INSERT INTO CZC_email (Offer, SendDate, Destinations, Copy, BannerURL)VALUES ('" & txtCampaignName.Text & "','" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "','" &DestinationsSelected & "','" & FreeTextBox2.Text & "', '"& txtBannerPath.Text & "')SELECT @.@.IDENTITY AS 'CZ_ID'"
And my update button has this SQL command:
Dim SqlMethod As String ="UPDATE CZC_email SET SendDate = '" &calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "', Offer = '"& txtCampaignName.Text & "',Destinations = '" &DestinationsSelected & "', BannerURL = '" & txtBannerPath.Text& "' WHERE CZ_ID = @.@.IDENTITY "
but it doesnt seem to be updating. anyone know what I'm doing wrong?
Cheers

(1) Use a stored proc.
(2) Use SCOPE_IDENTITY() instead of @.@.IDENTIY. Check books on line for the differences.
(3) Use Parameterized Queries to prevent SQL Injection atatcks (google for more info on this).|||Save the @.@.IDENTITY values you get from insert command & then in the update command pass the value returned from the insertion instead of @.@.IDENTITY|||I've tried saving the @.@.identity and scope_identity as a value ofvariable varCZ_ID by using the following code (i'm using the MS DAAB)
varCZ_ID = dataReader("SCOPE_IDENTITY")
or

varCZ_ID = dataReader("CZ_ID")

however, this is erroring 'Invalid attempt to read when no data is present.'
Any ideas what I'm doing wrong? this is really doing my head in!
Thanks
|||If you use a stored proc you could save a trip to the server and get back accurate Id.
|||yes I agree, I just wanted to get it working first off.
I managed to fix the problem by saving the @.@.IDENTITY into a session variable.

|||@.@.IDENTITY does not always give you the identity value that just gotgenerated by the insert statement. If multiple calls were made at thesame time it could mix up the Id's. So it is advised to useSCOPE_DENTITY() instead of @.@.IDENTITY.
|||Thats a good point, and something I am aware off.
For this particular application is not a big deal, as it only going to be used by two people - and not at the sametime.
However i will look to improve it soon, and that will be things I will do.
Cheers

Insert text file content into database

Hello everyone!

I'm having a problem with inserting the content of a text file into a Sql Server 2005 database.

I'm reading the text file into a dataset, and works fine. What I can't do is what I suspect is the simple part: Insert all the data into a table that has exactly the same configuration that the file. I've never worked with dataset's before, and I can't seem to find the answer to this!

This is what I have done so far:

Dim i2As Integer
 Dim jAs Integer
 Dim FileAs String = Server.MapPath("..\Docs\Facts\FORM_MAN_V3_1.txt")
Dim TableNameAs String ="Facts"Dim delimiterAs String ="9"Dim resultAs DataSet =New DataSet()
Dim sAs StreamReader =New StreamReader(File)
Dim columnsAs String() = s.ReadLine().Split(Chr(9))
 result.Tables.Add(TableName)
Dim strs1As String() = columns
For i2 = 0To CInt(strs1.Length) - 1
Dim colAs String = strs1(i2)
Dim addedAs Boolean =False
 Dim [next]As String =""Dim iAs Integer = 0
While Not added
Dim columnnameAs String =String.Concat(col, [next])
 columnname = columnname.Replace(Chr(9),"")
If Not result.Tables(TableName).Columns.Contains(columnname)Then result.Tables(TableName).Columns.Add(columnname) added =True
 Else i += 1 [next] =String.Concat("_", i.ToString())
End If
 End While
 Next i2
Dim strs2As String() = s.ReadToEnd().Split(Chr(13) & Chr(10).ToString())
For j = 0To CInt(strs2.Length) - 1
Dim itemsAs String() = strs2(j).Split(Chr(9))
 result.Tables(TableName).Rows.Add(items)
Next j

So now I have my dataset populated with all the information, but how can I insert it into the database?

If anyone can help I would appreciate very, very much!

Thank you

Paula

Hi There.

I think you should learn a bit about ado.net's table adapters. They can be created automatically with the dataset designer, and they can be used to select/insert/update/delete data between your dataset and its corresponding database.

Take a look at the howto's at the bottom of this article:

http://msdn2.microsoft.com/en-us/library/tkctt675(VS.80).aspx