Converting data to be inserted into a database

Hi,

I am using web matrix, and I am trying to insert a data into a MSDE database. I have used webmatrix to generate the update code, and it is executed when a button is pressed on the web page. but when the code is executed I get the error:

Syntax error converting the varchar value 'txtAmountSold.text' to a column of data type int.

So I added the following code to try to convert the data, but i am still getting the same error, with txtAmountSold.text replaced with "test"

dim test as integer
test = Convert.ToInt32(txtAmountSold.text)

Here is the whole of the function I am using:

Function AddItemToStock() As Integer

dim test as integer
test = Convert.ToInt32(txtAmountSold.text)

Dim connectionString As String = "server='(local)\Matrix'; trusted_connection=true; database='HawkinsComputers'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _
"], [Description], [image], [OnOffer], [OfferPr"& _
"ice], [OfferDescription], [AmountInStock], [AmountOnOrder], [AmountSold]) VALUES ('CatList.SelectedItem.text', 'txtType.text', 'txtname.text', 'txtmanufacturer.text'"& _
", convert(money,'txtPrice.text'), 'txtWeight.text', 'txtDescription.text', 'txtimage.text', 'txtOnOffer"& _
".text', convert(money,'txtOfferPrice.text'), 'txtOfferDescrip"& _
"tion.text', 'txtAmountInStock.text', 'txtAmountOnOrder.text', 'test')"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try

Return rowsAffected
End Function

Any help in solving this problem would be greatly appreciated, as I am really stuck for where to go next.The probably is that you are trying to send the literal value 'test' as a parameter value.

Please use this approach instead:
Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _
"], [Description], [image], [OnOffer], [OfferPr"& _
"ice], [OfferDescription], [AmountInStock], [AmountOnOrder],[AmountSold]) VALUES (@.CatList, @.Type,@.name, @.manufacturer, @.Price, @.Weight, @.Description, @.image, @.OnOffer, @.OfferPrice, @.OfferDescription, @.AmountInStock, @.AmountOnOrder, @.test)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.Parameters.Add("@.CatList,SqlDbType.VarChar,99).Value = CatList.SelectedItem.text
dbCommand.Parameters.Add("@.Type,SqlDbType.VarChar,50).Value =txtType.text
dbCommand.Parameters.Add("@.Name,SqlDbType.VarChar,30).Value =txtmanufacturer.text
' add all parameters in this manner
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Note that for each parameter you will need to use the appropriate SqlDbType. And if it's a character data type you will also need to specify the length.

Here are some links which should help you with using parameters:
Using Parameterized Query in ASP.NET, Part 1
Using Parameterized Query in ASP.NET, Part 2
Using Parameterized Queries in ASP.Net
How To: Protect From SQL Injection in ASP.NET|||Thanks alot for the reply, Those links will help alot aswell.

Converting a SQL Query to a Web page

I have a web site, and want to create a query on it using my current SQl database information, can someone explain to me how this is done? Or just send me a link to where I can get the information.
ThanksI am not entirely sure what you mean. Do you wish to
execute a query from the web site to retrieve data from
the database or send an update query to the database? Or
something else? Also, which server side technology are
you using for the web site? (PHP, ASP, ASP.NET, etc)
David Grant
>--Original Message--
>I have a web site, and want to create a query on it
using my current SQL database information, can someone
explain to me how this is done? Or just send me a link
to where I can get the information.
>Thanks
>.
>

Converting 2000 rdl files to 2005

Hi, how do I go about converting 2000 rdl files to 2005 format?
I have searched on the web and people recommend opening up the
existing rdl files in report designer or create a new microsoft report
project from visual studio 2005.
My problem is that when I open up the rdl files in 2005 I just have
the XML contents and no designer support. Also, I looked to create a
new report project from the File-->New-->Project prompt in VS2005 but
I do not have a Report Project available to me...
I'm probably missing something basic here so any help appreciated :)
Thanks
MarkusThe difference in the rdl files are minimal. Change the 2003 namespace to:
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
and there are few other properties that can be added or changed once you
figure out the missing VS2005 components.
Those properties or elements are: InteractiveWidth like:
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
and InteractiveHeight like:
<Width>10.125in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
The converse is true when you design a report in VS2005 and need to go back
to VS2003 for SQL Server 2000. I always just comment out InteractiveHeigth
and InteractiveWidth and change the "2005/01" to "2003/10".
Rob.
"MarkusJNZ@.gmail.com" wrote:
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||I think you have not installed the developper tools of SQL Server 2005
install the client tools from the SQL Server CD and check the options to
insure you install the client components for RS.
after this, Visual Studio will convert the reports automatically.
but an RS2000 report can be executed and rendered by reporting services
without any change.
<MarkusJNZ@.gmail.com> wrote in message
news:1177894103.172863.251460@.n59g2000hsh.googlegroups.com...
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||On Apr 30, 1:41 pm, "Jeje" <willg...@.hotmail.com> wrote:
> I think you have not installed the developper tools of SQL Server 2005
> install the client tools from the SQL Server CD and check the options to
> insure you install the client components for RS.
> after this, Visual Studio will convert the reports automatically.
> but an RS2000 report can be executed and rendered by reporting services
> without any change.
> <Markus...@.gmail.com> wrote in message
> news:1177894103.172863.251460@.n59g2000hsh.googlegroups.com...
>
> > Hi, how do I go about converting 2000 rdl files to 2005 format?
> > I have searched on the web and people recommend opening up the
> > existing rdl files in report designer or create a new microsoft report
> > project from visual studio 2005.
> > My problem is that when I open up the rdl files in 2005 I just have
> > the XML contents and no designer support. Also, I looked to create a
> > new report project from the File-->New-->Project prompt in VS2005 but
> > I do not have a Report Project available to me...
> > I'm probably missing something basic here so any help appreciated :)
> > Thanks
> > Markus- Hide quoted text -
> - Show quoted text -
Hi, thanks for your help; I went to install the client tools and I was
told that I already had them installed and that the install would not
go ahead as I was not changing anything on my local machine :-/
Any ideas welcome
Thanks
Markus|||Hi, I tried to do the install again and this was the message I
received
"Build version check:
Your upgrade is blocked because the existing component is a later
version than the version you are trying to upgrade to. To modify an
existing SQL Server component, go to Add or Remove Programs in Control
Panel, select the component you would like to modify, and then click
Change/Remove."
Thanks again
Markus|||Hi, is your rdl's files, created using report builder 2000? I get a doubt
that the designer doesn't shows means, just some simple reports are created
using RB and saved as reports. Pl confirm.
Amarnath
"MarkusJNZ@.gmail.com" wrote:
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||sorry, first time I see this message.
you have to check which applications are already installed, maybe you can
just change the installed components of an existing SQL 2005 installation.
else try to uninstall all your components and try again.
<MarkusJNZ@.gmail.com> wrote in message
news:1177910981.456177.237840@.o5g2000hsb.googlegroups.com...
> Hi, I tried to do the install again and this was the message I
> received
> "Build version check:
> Your upgrade is blocked because the existing component is a later
> version than the version you are trying to upgrade to. To modify an
> existing SQL Server component, go to Add or Remove Programs in Control
> Panel, select the component you would like to modify, and then click
> Change/Remove."
> Thanks again
> Markus
>|||On Apr 30, 9:19 pm, "Jeje" <willg...@.hotmail.com> wrote:
> sorry, first time I see this message.
> you have to check which applications are already installed, maybe you can
> just change the installed components of an existing SQL 2005 installation.
> else try to uninstall all your components and try again.
> <Markus...@.gmail.com> wrote in message
> news:1177910981.456177.237840@.o5g2000hsb.googlegroups.com...
>
> > Hi, I tried to do the install again and this was the message I
> > received
> > "Build version check:
> > Your upgrade is blocked because the existing component is a later
> > version than the version you are trying to upgrade to. To modify an
> > existing SQL Server component, go to Add or Remove Programs in Control
> > Panel, select the component you would like to modify, and then click
> > Change/Remove."
> > Thanks again
> > Markus- Hide quoted text -
> - Show quoted text -
Hi, thanks for your help everyone. It looks like I will have to
uninstall and then reintall MSSQL 2005 on my local machine :/
Thanks again
Markussqlsql

convert SQL 2005 express .mdf to SQL 2005

Hi Forum, I need some good info regards remote host requirements.

I dev web app using VS 2005 express edition and SQL Server Management Studio express. Everything is ready to transfer to host running SQL 2005.

My ? do I have to convert my SQL 2005 express .mdf to a SQL 2005 file type. (hosting company state I do, then save as .bak)

much appreciate good data, Paul

A mdb file is Access based, an mdf file is the data file of SQL Server. They are not convertible. You will have to import the data from access or export / upsize from access to make them stored on the server.

|||

check out this link

http://aspalliance.com/888

|||

There are two easy solutions:

1) Attach you .mdf file to your database with the hosting company.

2) Make a backup for your database (e.g. on the local machine) and restore it in your database with the hosting company.

Good luck.

|||

CS4Ever:

There are two easy solutions:

1) Attach you .mdf file to your database with the hosting company.

2) Make a backup for your database (e.g. on the local machine) and restore it in your database with the hosting company.

Good luck.

There are two more solutions but some time it does not work due to security issues from the hosting company.

1) Use SSIS (SQL Server Integration Services) a wizard that will copy your data and objects from local machine to the hosting server.

2) Use Database Copy Wizard.

Good luck.

|||Thanks everyone for info, I ended up creating a blank db on host server and configing through SQL server manager, cheers P|||

What do you mean by:

plkilroy:

configing through SQL server manager

Please mark Answer the post(s) helped you, so it will help other to get the soltion.

Thanks.

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

|||See http://www.360data.nl/EN/Docs/080123_XML.aspx for an example parsing Excel-generated XML into a SQL db.

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

Convert MS Excel to XML and then insert all data records into MS SQL

Hi,

I am developing a web page for users to input data records. I am seeking the fastest way that users can upload their MS Excel files and the system can help data insertion into one data table. I know XML can insert data records into SQL database easily. Could any one give me some ideas how to perform this issue? Thanks a lot.

If you are using excel, you don't need to transform to xml. You can load data directly from excel file.

INSERT YourTable(...)

SELECT ...
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\YourExcelFile.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[YourSheetName]

|||

1) Save your excel file in XML format

2) create YourTable(xml_excel xml)

3) Run

INSERT YourTable(xml_excel)

select cast(x as xml) from openrowset(bulk 'dir_path_to_excel_xml_file', single_blob) as t(x)

|||

Thanks a lot! Could there be any sample code somewhere? I hardly find one suit into my case. The process should be:

1) Upload Excel file (Will be grateful if users can directly import.)

2) System Import data from specific data column in Excel to MS SQL table

A pre-define Excel workbook is provided for users.|||

I would like to convert the MS Excel file to XML on the server, rather than training users to export in XML format. Also, users may not all have Excel 2003.

Is there a way to convert on a server?

Thanks

|||

Kenneth,

Another method is to use a third party tool, like the FarPoint Spread control (www.FarPointSpread.com) to load the Excel file on your web server, without needing access to Excel. Then, you have the data and formatting inspreadsheet format that you can do what you need to with. From here, I would suggest creating a DataSet object with the data and then opening a connection to the Sql database to write your DataRows from the DataSet you created.

Scott Shorter
FarPoint Technologies

|||

Hi can u please tell me where this code should be written in an excel sheet.I am using macros to select data from sql and want to export the updated data from excel to sql database.

Regards,

Shiva

convert mdf to mdb

hi all,

I'm using visual web developer and created a project with a mdf data file. how can I convert this file to MS ACCESS mdb file?

Thanks

Normally migration proceeds the other way!

Open your SQL Server management tool, select the database, go tasks, export and on the output select a pre-existing access database.

|||

I just installed Visual Web Developer and SQL Server 2005 Express Edition. Seems cannot find the tool you mentioned..

If programatically convert it I'm concerning some reserved words problem like "Value" in SQL server is ok while ion ACCESS is a reserved word..

And DateTime in SQL is one field while in ACCESS is in 2 fields..

|||

Hi stardust25,

TATWORTH :

Normally migration proceeds the other way!

Open your SQL Server management tool, select the database, go tasks, export and on the output select a pre-existing access database.

The solution TATWORTH has suggested you should work on SQL Server 2005. But since you haveExpression Edition installed, things are a little different.Based on my understanding, SQL Express doesnot have DTS/SSIS shipped with. We can only useBCP, Bulk Insert, Openrowset, Code..etc to do export/export (based on the datatype you want to deal with). You can take a look at this thread I answered recentlyhttp://forums.asp.net/t/1139369.aspx, I hope it can help you better understand the converstion process.thanks