help with OPENXML

I'm having problem with inserting xml into sql server.
I have procedure:
------
CREATE PROC sp_insert_BGListaIzvj @.strXML varchar(8000)
AS
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
INSERT INTO BGListaIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj', 2)
WITH BGListaIzvj
INSERT INTO BGIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj/BGIzvj', 2)
WITH BGIzvj
EXEC sp_xml_removedocument @.hDoc
GO
------
and then I save xml data from dataset:
------
Dim connStr As String =
"Provider=SQLOLEDB;Server=-BERNARD-;database=form;Integrated Security=SSPI"
Dim cmd As New SqlXmlCommand(connStr)
cmd.RootTag = "ROOT"
cmd.CommandType = SqlXmlCommandType.Sql
cmd.CommandText = "SELECT L.ListaIzvjID, L.FirmaID, L.BlgID, L.RedBroj,
L.Razdoblje, L.PocStanje, L.ZavStanje, L.Fin, L.Godina, L.Mjesec, L.Sn,
I.ListaIzvjID AS ListaIzvjID2, I.BlgID AS BlgID2, I.Datum, I.Primljen,
I.Opis, I.Dokum, I.Konto, I.Duguje, I.Potrazuje, I.U, I.Jed, I.Odj, I.Tip,
I.BR, I.Godina AS Godina2 FROM BGListaIzvj L INNER JOIN (SELECT ListaIzvjID,
BlgID, Datum, Primljen, Opis, Dokum, Konto, Duguje, Potrazuje, U, Jed, Odj,
Tip, BR, Godina FROM BGIzvj) I ON I.ListaIzvjID = L.ListaIzvjID WHERE
(L.FirmaID = '13') FOR XML AUTO, ELEMENTS"
Dim DA As New SqlXmlAdapter(cmd)
Dim ds As New DataSet
DA.Fill(ds)
ds.WriteXml("D:\BGListaIzvj.xml")
------
and this is the xml file:
------
<?xml version="1.0" standalone="yes"?>
<ROOT>
<L>
<ListaIzvjID>16</ListaIzvjID>
<FirmaID>13</FirmaID>
<BlgID>7</BlgID>
<RedBroj>1</RedBroj>
<Razdoblje>01.01.2004 do 31.01.2004</Razdoblje>
<PocStanje>328.79</PocStanje>
<ZavStanje>143.18</ZavStanje>
<Fin>1</Fin>
<Godina>2004</Godina>
<Mjesec>01.2004</Mjesec>
<Sn>0</Sn>
<BGIzvj>
<ListaIzvjID2>16</ListaIzvjID2>
<BlgID2>7</BlgID2>
<Datum>2004-01-07T00:00:00</Datum>
<Primljen>-</Primljen>
<Opis>plaa za 12/03 za Amaliju Cigler</Opis>
<Dokum>Upl1</Dokum>
<Konto>1009</Konto>
<Duguje>222.4</Duguje>
<Potrazuje>0</Potrazuje>
<U>Zagreb</U>
<Jed>0</Jed>
<Odj>0</Odj>
<Tip>U</Tip>
<BR>1</BR>
<Godina2>2004</Godina2>
</BGIzvj>
</L>
<L>
<ListaIzvjID>17</ListaIzvjID>
<FirmaID>13</FirmaID>
<BlgID>7</BlgID>
<RedBroj>2</RedBroj>
<Razdoblje>01.06.2004 do 30.06.2004</Razdoblje>
<PocStanje>143.18</PocStanje>
<ZavStanje>1797.79</ZavStanje>
<Fin>1</Fin>
<Godina>2004</Godina>
<Mjesec>06.2004</Mjesec>
<Sn>0</Sn>
<BGIzvj>
<ListaIzvjID2>17</ListaIzvjID2>
<BlgID2>7</BlgID2>
<Datum>2004-06-15T00:00:00</Datum>
<Primljen>gotovina</Primljen>
<Opis>mat. trokove i isplata PN</Opis>
<Dokum>Upl2</Dokum>
<Konto>1009</Konto>
<Duguje>15000</Duguje>
<Potrazuje>0</Potrazuje>
<U>Zagreb</U>
<Jed>0</Jed>
<Odj>0</Odj>
<Tip>U</Tip>
<BR>2</BR>
<Godina2>2004</Godina2>
</BGIzvj>
</L>
</ROOT>
------
Now, I'm trying to save that xml into sql database with this:
Dim sr As New StreamReader("d:\BGListaIzvj.xml")
Dim strXML As String
strXML = sr.ReadToEnd
sr.Close()
cmd.Parameters("@.strXML").Value = strXML
SqlConnection1.Open()
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
SqlConnection1.Close()
but I'm getting error, XML Parsing error: An Invalid character found in text
content.
I tried to put <?xml version="1.0" encoding="utf-8" ?> in first row of xml
file, but it won't work.
And why is that <L> </L> there?
Can somebody help me?
thanks!
Try NTEXT for the type of @.strXML (I assume that the data is UTF-16
encoded).
The L element is there because of your FOR XML query.
Best regards
Michael
"green_eye" <bernard@.form.hr> wrote in message
news:clt9kv$cnq$1@.ls219.htnet.hr...
> I'm having problem with inserting xml into sql server.
> I have procedure:
> ------
> CREATE PROC sp_insert_BGListaIzvj @.strXML varchar(8000)
> AS
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
>
> INSERT INTO BGListaIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj', 2)
> WITH BGListaIzvj
>
> INSERT INTO BGIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj/BGIzvj', 2)
> WITH BGIzvj
>
> EXEC sp_xml_removedocument @.hDoc
> GO
> ------
>
> and then I save xml data from dataset:
> ------
> Dim connStr As String =
> "Provider=SQLOLEDB;Server=-BERNARD-;database=form;Integrated
> Security=SSPI"
> Dim cmd As New SqlXmlCommand(connStr)
> cmd.RootTag = "ROOT"
> cmd.CommandType = SqlXmlCommandType.Sql
>
> cmd.CommandText = "SELECT L.ListaIzvjID, L.FirmaID, L.BlgID, L.RedBroj,
> L.Razdoblje, L.PocStanje, L.ZavStanje, L.Fin, L.Godina, L.Mjesec, L.Sn,
> I.ListaIzvjID AS ListaIzvjID2, I.BlgID AS BlgID2, I.Datum, I.Primljen,
> I.Opis, I.Dokum, I.Konto, I.Duguje, I.Potrazuje, I.U, I.Jed, I.Odj, I.Tip,
> I.BR, I.Godina AS Godina2 FROM BGListaIzvj L INNER JOIN (SELECT
> ListaIzvjID, BlgID, Datum, Primljen, Opis, Dokum, Konto, Duguje,
> Potrazuje, U, Jed, Odj, Tip, BR, Godina FROM BGIzvj) I ON I.ListaIzvjID =
> L.ListaIzvjID WHERE (L.FirmaID = '13') FOR XML AUTO, ELEMENTS"
>
> Dim DA As New SqlXmlAdapter(cmd)
> Dim ds As New DataSet
> DA.Fill(ds)
>
> ds.WriteXml("D:\BGListaIzvj.xml")
>
> ------
>
> and this is the xml file:
> ------
> <?xml version="1.0" standalone="yes"?>
> <ROOT>
> <L>
> <ListaIzvjID>16</ListaIzvjID>
> <FirmaID>13</FirmaID>
> <BlgID>7</BlgID>
> <RedBroj>1</RedBroj>
> <Razdoblje>01.01.2004 do 31.01.2004</Razdoblje>
> <PocStanje>328.79</PocStanje>
> <ZavStanje>143.18</ZavStanje>
> <Fin>1</Fin>
> <Godina>2004</Godina>
> <Mjesec>01.2004</Mjesec>
> <Sn>0</Sn>
> <BGIzvj>
> <ListaIzvjID2>16</ListaIzvjID2>
> <BlgID2>7</BlgID2>
> <Datum>2004-01-07T00:00:00</Datum>
> <Primljen>-</Primljen>
> <Opis>plaa za 12/03 za Amaliju Cigler</Opis>
> <Dokum>Upl1</Dokum>
> <Konto>1009</Konto>
> <Duguje>222.4</Duguje>
> <Potrazuje>0</Potrazuje>
> <U>Zagreb</U>
> <Jed>0</Jed>
> <Odj>0</Odj>
> <Tip>U</Tip>
> <BR>1</BR>
> <Godina2>2004</Godina2>
> </BGIzvj>
> </L>
> <L>
> <ListaIzvjID>17</ListaIzvjID>
> <FirmaID>13</FirmaID>
> <BlgID>7</BlgID>
> <RedBroj>2</RedBroj>
> <Razdoblje>01.06.2004 do 30.06.2004</Razdoblje>
> <PocStanje>143.18</PocStanje>
> <ZavStanje>1797.79</ZavStanje>
> <Fin>1</Fin>
> <Godina>2004</Godina>
> <Mjesec>06.2004</Mjesec>
> <Sn>0</Sn>
> <BGIzvj>
> <ListaIzvjID2>17</ListaIzvjID2>
> <BlgID2>7</BlgID2>
> <Datum>2004-06-15T00:00:00</Datum>
> <Primljen>gotovina</Primljen>
> <Opis>mat. trokove i isplata PN</Opis>
> <Dokum>Upl2</Dokum>
> <Konto>1009</Konto>
> <Duguje>15000</Duguje>
> <Potrazuje>0</Potrazuje>
> <U>Zagreb</U>
> <Jed>0</Jed>
> <Odj>0</Odj>
> <Tip>U</Tip>
> <BR>2</BR>
> <Godina2>2004</Godina2>
> </BGIzvj>
> </L>
> </ROOT>
> ------
> Now, I'm trying to save that xml into sql database with this:
>
> Dim sr As New StreamReader("d:\BGListaIzvj.xml")
> Dim strXML As String
> strXML = sr.ReadToEnd
> sr.Close()
>
> cmd.Parameters("@.strXML").Value = strXML
> SqlConnection1.Open()
> Try
> cmd.ExecuteNonQuery()
> Catch ex As Exception
> MsgBox(ex.Message)
> End Try
> SqlConnection1.Close()
>
> but I'm getting error, XML Parsing error: An Invalid character found in
> text content.
> I tried to put <?xml version="1.0" encoding="utf-8" ?> in first row of
> xml file, but it won't work.
> And why is that <L> </L> there?
>
> Can somebody help me?
>
> thanks!
>
>
|||OK, thanks! It works with nText type, but I have some other problem.
With this procedure:
CREATE PROC sp_insert_BGListaIzvj @.strXML nText
AS
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
INSERT INTO BGListaIzvj
SELECT *
FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj')
WITH BGListaIzvj
INSERT INTO BGIzvj
SELECT *
FROM OPENXML(@.hDoc, '//BGIzvj')
WITH BGIzvj
EXEC sp_xml_removedocument @.hDoc
GO
and this XML file:
<?xml version="1.0" standalone="yes"?>
<ROOT>
<BGListaIzvj ListaIzvjID="16" FirmaID="13" BlgID="7" RedBroj="1"
Razdoblje="01.01.2004 do 31.01.2004" PocStanje="328.79" ZavStanje="143.18"
Fin="1" Godina="2004" Mjesec="01.2004" Sn="0">
<BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
Primljen="-" Opis="plaa za 12/03 za Amaliju Cigler" Dokum="Upl1"
Konto="1009" Duguje="222.4" Potrazuje="0" U="Zagreb" Jed="0" Odj="0" Tip="U"
BR="1" Godina2="2004" />
<BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
Primljen="Ljubomir Babi" Opis="plaa za Amaliju Cigler 12/03" Dokum="Ispl1"
Konto="2304" Duguje="0" Potrazuje="222.4" U="Zagreb" Jed="3" Odj="0" Tip="I"
BR="1" Godina2="2004" />
</BGListaIzvj>
<BGListaIzvj ListaIzvjID="17" FirmaID="13" BlgID="7" RedBroj="2"
Razdoblje="01.06.2004 do 30.06.2004" PocStanje="143.18" ZavStanje="1797.79"
Fin="1" Godina="2004" Mjesec="06.2004" Sn="0">
<BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-15T00:00:00"
Primljen="gotovina" Opis="mat. trokove i isplata PN" Dokum="Upl2"
Konto="1009" Duguje="15000" Potrazuje="0" U="Zagreb" Jed="0" Odj="0" Tip="U"
BR="2" Godina2="2004" />
<BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-30T00:00:00"
Primljen="Ljubomir Babi" Opis="PN 3,4,5,7 -dnevnice" Dokum="Ispl5"
Konto="4600" Duguje="0" Potrazuje="595" U="Zagreb" Jed="3" Odj="0" Tip="I"
BR="5" Godina2="2004" />
</BGListaIzvj>
</ROOT>
I get rows in second table (BGIzvj) with NULL values in columns ListaIzvjID,
BlgID and Godina. These three tables are the same as in BGListaIzvj table.
Is that a problem? Is there way to fix this?
Thanks!
|||Hi Bernard
What are the schemas of your relational tables?
Since you get the rows, your path expressions find the nodes that map to
rows, but it looks like that the relative paths implied by the relational
schema does not find any values for the nodes where you get NULL.
This probably means that your Schema names and attribute names do not fit.
Best regards
Michael
"Bernard" <bernard@.form.hr> wrote in message
news:cm0576$r2e$1@.ls219.htnet.hr...
> OK, thanks! It works with nText type, but I have some other problem.
> With this procedure:
> ----
> CREATE PROC sp_insert_BGListaIzvj @.strXML nText
> AS
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.strXML
>
> INSERT INTO BGListaIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '/ROOT/BGListaIzvj')
> WITH BGListaIzvj
>
> INSERT INTO BGIzvj
> SELECT *
> FROM OPENXML(@.hDoc, '//BGIzvj')
> WITH BGIzvj
>
> EXEC sp_xml_removedocument @.hDoc
> GO
> ----
>
> and this XML file:
> ----
> <?xml version="1.0" standalone="yes"?>
> <ROOT>
> <BGListaIzvj ListaIzvjID="16" FirmaID="13" BlgID="7" RedBroj="1"
> Razdoblje="01.01.2004 do 31.01.2004" PocStanje="328.79" ZavStanje="143.18"
> Fin="1" Godina="2004" Mjesec="01.2004" Sn="0">
> <BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
> Primljen="-" Opis="plaa za 12/03 za Amaliju Cigler" Dokum="Upl1"
> Konto="1009" Duguje="222.4" Potrazuje="0" U="Zagreb" Jed="0" Odj="0"
> Tip="U" BR="1" Godina2="2004" />
> <BGIzvj ListaIzvjID2="16" BlgID2="7" Datum="2004-01-07T00:00:00"
> Primljen="Ljubomir Babi" Opis="plaa za Amaliju Cigler 12/03"
> Dokum="Ispl1" Konto="2304" Duguje="0" Potrazuje="222.4" U="Zagreb" Jed="3"
> Odj="0" Tip="I" BR="1" Godina2="2004" />
> </BGListaIzvj>
> <BGListaIzvj ListaIzvjID="17" FirmaID="13" BlgID="7" RedBroj="2"
> Razdoblje="01.06.2004 do 30.06.2004" PocStanje="143.18"
> ZavStanje="1797.79" Fin="1" Godina="2004" Mjesec="06.2004" Sn="0">
> <BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-15T00:00:00"
> Primljen="gotovina" Opis="mat. trokove i isplata PN" Dokum="Upl2"
> Konto="1009" Duguje="15000" Potrazuje="0" U="Zagreb" Jed="0" Odj="0"
> Tip="U" BR="2" Godina2="2004" />
> <BGIzvj ListaIzvjID2="17" BlgID2="7" Datum="2004-06-30T00:00:00"
> Primljen="Ljubomir Babi" Opis="PN 3,4,5,7 -dnevnice" Dokum="Ispl5"
> Konto="4600" Duguje="0" Potrazuje="595" U="Zagreb" Jed="3" Odj="0" Tip="I"
> BR="5" Godina2="2004" />
> </BGListaIzvj>
> </ROOT>
> ----
>
> I get rows in second table (BGIzvj) with NULL values in columns
> ListaIzvjID, BlgID and Godina. These three tables are the same as in
> BGListaIzvj table. Is that a problem? Is there way to fix this?
>
> Thanks!
>
>
>

Help with inserting multiple records using a CSV value.

I have the Temporary table:

ItemDetailID (int)
FieldID (int)
FieldTypeID (int)
ReferenceName (Varchar(250))
[Value] (varChar(MAX))

in one instance Value might equal: "1, 2, 3, 4"

This only happens when FieldTypeID = 5.

So, I need an insert query for when FieldTypeID = 5, to insert 5 rows into the TableFieldListValues(ItemDetailID, [value])

I have created a function to split the [Value] into a table of INTs

Any Advice?

If your function returns a table type data, loop through the table and do an INSERT for each row.

|||

I would love to do that... but... I can program my way out of a box using C#... with SQL.. i could probably take a baby step to the bathroom :\

Do you know of any links/resources/source that could show me how? I've googled like crazy, but no luck :(

|||

You could do an :

(1) Declare a table variable with an additional column Processed tinyint.

(2) INSERT INTO @.table

SELECT dbo.someFunction

(3) Loop through the table.

WHILE EXISTS (SELECT * FROM @.table Where Procesed = 0)

Begin

Get the values from the @.table

Insert into the Original table

update @.table set processed = 1 Where Condition

End

|||

I think I understand...

While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?

|||

RTernier:

I think I understand...

While Loops, So when you do the:
WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

It goes through it row by row, sort of like a Foreach(DataROw row in DataTable) in C#?

Yes.

|||

That would work. Now another question (Yea, I'm not that strong in SQL :P )

While I go through the WHILE loop,

Is there a way I can grab the values of the loop I'm going through?

Example:

WHILE EXISTS(SELECT * FROM @.Table WHERE Processed = 0)
BEGIN

END

====

I could do this right:

WHILE EXISTS(SELECT * FROM @.Table T WHERE Processed = 0)
BEGIN

PRINT T.MyColumn
END

===

if not, how can I directly access the values from T?

|||

If the values returned by your function are unique, then you can use a MIN(Id) to get each id, else you can add an IDENTITY column to your table variable and use that to navigate through each row.

Decare @.rowid int

WHILE ...

Begin

SELECT @.rowid = MIN(id) FROM @.Table Where Processed = 0

INSERT INTO ...original table

Update @.t Set Processed = 1 Where Id = @.Rowid

End

Help with inserting array contents to SQL Server 2000

I've been doing this in Access, but cannot find the answer to how to do it with SQL Server.

From a web form, a user can select a number of different dates. The selected dates are held as text (not DateTime) in an ArrayList.

Clicking the Submit button writes the contents of the form to a database table.

This works for Access:

insSQL &= "VALUES (@.typEvent, @.starts, @.ends, @.starts, @.ends, @.attend, @.title, @.room, @.department, @.contact, @.address, @.telephone, @.email, @.telefax, "
For i = 0 to datesArray.Count - 1
insSql &= datesArray.Item(i)
Next i

insSQL &= "VALUES (@.typEvent, @.starts, @.ends, @.starts, @.ends, @.attend, @.title, @.room, @.department, @.contact, @.address, @.telephone, @.email, @.telefax, "
For i = 0 to datesArray.Count - 1
insSql &= "#" & datesArray.Item(i) & "#, "
Next i

It doesn't work for SQL Server, and when trying to insert the value "01/29/2007" I get the error message: "The name '#1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

I have also tried the line:

For i = 0 to datesArray.Count - 1
insSql &= satesArray.Item(i)
Next i

and get: "Incorrect syntax near the keyword 'VALUES'."

I'm not sure where to find the information to correct my error.

Any help would be appreciated.

Tinker

On a quick review, before looking into the code, you dont need to enclose "#" around the dates like you do for Access. So remove that and give it a shot again.|||

And your dates have to be in single quotes '01/29/2007' to work correctly because you pass them as string, and be sure that your date format is equal to data format strings used by server because if server uses dd/mm/yyyy this date will fail or if you server use mm-dd-yyyy it will fail also.

Best solution is to remove / and - from string, do test and you will see results

see posthttp://forums.asp.net/thread/1553054.aspx for examples how data formating could crash.

|||

Thank you -- and you, too, ndinakar. I was able to remember that the #'s are required for Access, but couldn't find the information on using single quotes for SQL server on my own.

That solved it, and I am grateful for your help.

Tinker

Help with inserting a new record into database - error Must declare the scalar variable "@

Hi,

Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error :

Must declare the scalar variable "@.BookMarkArrayA".

when it reaches the .insert command, I've tried using a local variable temp in place of the array element and.ToString , but still get the same error

This is the code :

PublicSub NewCustomer()

Dim tempAsString =" "

Dim IDAsInteger = 1

'Restore the array from the view state

BookMarkArrayA =Me.ViewState("BookMarkArrayA")

temp = BookMarkArrayA(6)

Dim CustomerAs SqlDataSource =New SqlDataSource()

Customer.ConnectionString = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString()

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

Customer.Insert()

EndSub

Cheers

Ken

I'm not sure where you got the (6) syntax from?

Try this. Change these 3 lines:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

to this and you should have better luck:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Integer).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value =@.BookMarkArrayA)

|||

Terri,

Thanks for the reply, it didn't compile as the SqlDbType wasn't recognised.

The origional error was @.BookMarkArray was an undeclared scalar variable, which suggests to me that this part of the command requires a pointer to the actual variable BookMarkArray denoted by putting the @. symbol first.

The (6) syntak by the way, was the element of the array I wanted to load into the DB.

The code fits with other examples I've looked up, I'm proberbly missing something simple.

Ken

|||Boy did I screw up the code. I am sorry about that. Here's what it *should* be:

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.Program)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Int).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value = BookMarkArray(6)

|||

Terri,

I still get the error that SqlDbType is not declared, am i missing inheriting a library or some thing

Ken

|||Well, try qualifying it by adding the namespace in front and see if that takes care of it.

SqlClient.SqlDbType.Int|||

Terri,

I get the error SqlClient not declared, so I assume that I'm not inheriting something.

I tried the following code which worked, but I can't find a method to change the String value 'Test Program' into a parameter:

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText =

"INSERT Into Customer (Program) VALUES ('Test Program')"

.Connection = sqlConnection1

EndWith

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

Ken

|||Try that code block like this (updates in pink):

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText = "INSERT Into Customer (Program) VALUES (@.Program)"

.Connection = sqlConnection1

EndWith

cmd.Parameters.Add("@.Program", System.Data.SqlDbType.VarChar, 99).Value = "Test Program"

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

|||

Terri,

Thanks, that worked, althrough I'd tried variations of adding parameters before without success.

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient and when you look at the methods, it says you must reference this namespace but don't tell you how.

Ken

|||

KenWalker:

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient

If you are using code inline (ie, not a separate .vb file), put this at the top of the page, right below the @.Page directive:

<%@. Import Namespace="System.Configuration" %>

If you are using code beside/behind, put this at the very top of your code:

Imports System.Data.Client

|||

I'm lazy, so I put it in web.config under the system.web section:

<pagestheme="default">

<namespaces>

<addnamespace="System.Data"/>

<addnamespace="System.Data.SqlClient"/>

<addnamespace="System.Configuration.ConfigurationManager"/>

</namespaces>

</pages>

|||

Thanks Terry, I have solved the problem using -Dim cmdAsNew System.Data.SqlClient.SqlCommand and then adding the parameters that way.

Ken