Re: SQL String to insert values into a table that are C# variables. - Database Discussion Boards

Eric Dahlvang11-Jul-06 7:48

11-Jul-06 7:48

Shrinking a Database [^]

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

Re: SQL Server files very large

dptalt13-Jul-06 3:09

dptalt

13-Jul-06 3:09

Turns out the log file has become very large. How can I purge this file? Can it be done from the enterprise manager?

Re: SQL Server files very large

Eric Dahlvang13-Jul-06 3:52

Eric Dahlvang

13-Jul-06 3:52

I think this Mike Dimmick answers your questions:clickety[^]

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

SQL String to insert values into a table that are C# variables.

DavidSAtWork11-Jul-06 4:51

DavidSAtWork

11-Jul-06 4:51

Hello,
Im trying to create a SQL string that will insert values (Values that were gathered from a ASP.NET web form) into a table. The values are C# variables. I can insert manually typed values 'string' but when I try to make it a variable , I get an error. ORA-00984: column not allowed here. I either need the correct syntax for the sql string (values) or need to know the correct method for inserting C# variables into an oracle database.
Using MSVisual Studio 2005, ASP.NET webform, C#, Oracle 9i DB.

Sample of the code Im using...
protected void SubmitBtn_Click(object sender, EventArgs e)
{

String TodayStamp = DateTime.Today.ToString();
String EnteredFirstName = FirstNameTxtBox.Text;
String EnteredLastName = LastNameTxtBox.Text;
String EnteredCompany = CompanyNameTxtBox.Text;
String EnteredEmail = EmailAddTxtBox.Text;
String EnteredCity = CityTxtBox.Text;
String EnteredState = StateDropDownList.SelectedValue;
String EnteredCountry = CountryDropDownList.SelectedValue;

string strSQL = "INSERT INTO table(Firstname, Lastname, Company, Email, City, State, ActivityDate, Country) VALUES (EnteredFirstName, EnteredLastName, EnteredCompany, EnteredEmail, EnteredCity, EnteredState, TodayStamp, EnteredCountry )" ;

OdbcConnection conn = new OdbcConnection(ConfigurationManager.ConnectionStrings["DBConn"].ConnectionString);

OdbcCommand myODBCCommand = new OdbcCommand( strSQL, conn);

try
{
conn.Open();
myODBCCommand.ExecuteNonQuery(); **Errors Here**
}

catch
{
}
finally
{
conn.Close();
}
}

Can anyone help please??
thanks...

David

Re: SQL String to insert values into a table that are C# variables.

albCode11-Jul-06 5:00

albCode

11-Jul-06 5:00

string strSQL = "INSERT INTO table(Firstname, Lastname, Company, Email, City, State, ActivityDate, Country) VALUES ('"+ <br />
EnteredFirstName +"','"+<br />
 EnteredLastName + "','" +  <br />
EnteredCompany+ "','" + <br />
EnteredEmail+ "','" + <br />
EnteredCity+ "','" + <br />
EnteredState+ "','" + <br />
TodayStamp+ "','" +<br />
EnteredCountry +"')" ;

Re: SQL String to insert values into a table that are C# variables.

Colin Angus Mackay11-Jul-06 5:05

Colin Angus Mackay

11-Jul-06 5:05

SQL Injection Attacks[^] - You might like to look into preventing them! Sigh | :sigh:

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Re: SQL String to insert values into a table that are C# variables.

albCode11-Jul-06 5:12

albCode

11-Jul-06 5:12

Thanks for yourm warning.
Sure I have read about it.

Re: SQL String to insert values into a table that are C# variables.

Colin Angus Mackay11-Jul-06 5:08

Colin Angus Mackay

11-Jul-06 5:08

I don't know Oracle, but I can tell you that the previous answer you got will make your code susceptable to SQL Injection Attacks[^]. You should look into how to create parameterised queries for Oracle (Also, are you aware that there is an Oracle provider for .NET so you don't need to use ODBC)

Re: SQL String to insert values into a table that are C# variables.

User 171649211-Jul-06 5:38

User 1716492

11-Jul-06 5:38

Oracle tutorials etc... where you can also find intimate details of error ORA-00984

[^]

modified 1-Aug-19 21:02pm.

Re: SQL String to insert values into a table that are C# variables.

DavidSAtWork11-Jul-06 5:52

DavidSAtWork

11-Jul-06 5:52

Thanks both....
I will start with the first solution in development and then work on the attacks problem before I move to production.

David

Re: SQL String to insert values into a table that are C# variables.

kumarprabhakar7412-Jul-06 0:56

kumarprabhakar74

12-Jul-06 0:56

In above code, strSQL does not translate variable value because it is included instring.

Please releace above string with following strSQl

string strSQL = "INSERT INTO table(Firstname, Lastname, Company, Email, City, State, ActivityDate, Country) VALUES('" + EnteredFirstName + "','" + EnteredLastName + "','" + EnteredCompany + "','" + EnteredEmail + "','" + EnteredCity + "','" + EnteredState + "','" + TodayStamp + "','" + EnteredCountry + "')";

myODBCCommand.ExecuteNonQuery() will also fetch error if your passing value doesnot match with database datatype

Kumar

SQL 2005 Backup Problem

DineshSharma11-Jul-06 1:43

DineshSharma

11-Jul-06 1:43

Dear All
I have taken a backup for my database and trying to restore it on another computer .....but its showing me error while i m restoring this database...
error is Too many backup devices specified for backup or restore only 64 are allowed.

pls give me a solution if someone knows abt that... Confused | :confused:

Dinesh Sharma

Re: SQL 2005 Backup Problem

albCode11-Jul-06 5:11

albCode

11-Jul-06 5:11

http://www.1planhost.com/support/s_sql2000dbbak.asp[^]

auto upload/export of a downloaded CSV file to SQLSERVER on the server

amitcoder8310-Jul-06 19:51

amitcoder83

10-Jul-06 19:51

my application is actually a web application where i manually upload CSV file to the database.but i want to automate the process,so i have thought of a second stage to this application i.e through a windows application i would download the CSV file from the NSEIndia.com site automatically and then upload the same to SQLServer on the server.i am facing a problem with upload i.e exporting the data to the database.it would be a great help to me if any one could provide me a solution either in VB.Net code or C# code.

AMIT(TATA CMC)

Re: auto upload/export of a downloaded CSV file to SQLSERVER on the server

Paul Conrad10-Jul-06 21:41

Paul Conrad

10-Jul-06 21:41

Where do i start? [modified]

[Marc]10-Jul-06 18:41

[Marc]

10-Jul-06 18:41

Hey everybody Big Grin | :-D

Ok so here i am, programming a bit for 2 years now (VB.Net 2003). And now i got a lot of data, like 30000 items with many properties and stuff. From all those i want to select only a couple based on those properties and then do stuff with it. So i was thinking about a database. But were do start? I was reading some tutorial and they started with a connection string, but how do i know which connection i have? Do i have to install something?

Database stuff is a huge black hole in my head, so any help would be welcome Smile | :)

"..Commit yourself to quality from day one..it's better to do nothing at all than to do something badly.."
-- Mark McCormick

|| Fold With Us! || Pensieve || VG.Net ||

-- modified at 0:53 Tuesday 11th July, 2006

Re: Where do i start?

Colin Angus Mackay10-Jul-06 20:17

Colin Angus Mackay

10-Jul-06 20:17

[Marc] wrote:
Do i have to install something?

Yes, you need to install a database.

You might also wan't to get a book on ADO.NET to help you with this "stuff"

Re: Where do i start?

Eric Dahlvang11-Jul-06 6:04

Eric Dahlvang

11-Jul-06 6:04

Some of these are good:
http://www.codeproject.com/vb/net/#Data+Access[^]

--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters

First record in list creates 'System.Data.DataRowView'

Glen Harvy10-Jul-06 15:31

Glen Harvy

10-Jul-06 15:31

Hi,

Using VS2003 and C#, I have created oleDbAdapaters to fill a data set and used a combo box to allow easy selection of choices for amending record fields. Here's the code for the combobox:

// comboBoxMembershipLevel
//
this.comboBoxMembershipLevel.DataBindings.Add(new System.Windows.Forms.Binding("SelectedItem", this.myClubDataSet12,"MembershipLevels.MembershipLevelName"));
this.comboBoxMembershipLevel.DataBindings.Add(new System.Windows.Forms.Binding("SelectedValue", this.myClubDataSet12, "MemberDetails.MembershipLevel"));
this.comboBoxMembershipLevel.DataSource = this.myClubDataSet12.MembershipLevels;
this.comboBoxMembershipLevel.DisplayMember = "MembershipLevelName";
this.comboBoxMembershipLevel.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.comboBoxMembershipLevel.Location = new System.Drawing.Point(584, 80);
this.comboBoxMembershipLevel.Name = "comboBoxMembershipLevel";
this.comboBoxMembershipLevel.Size = new System.Drawing.Size(121, 21);
this.comboBoxMembershipLevel.TabIndex = 40;
this.comboBoxMembershipLevel.ValueMember = "MembershipLevel";
//

The table "MembershipLevels" has three fields - ID(autogenerated key), MembershipLevel(int) and MembershipLevelName(text). The MemebershipLevel is stored for each record in the table MembersDetails.MembershipLevel(int).

When I select the first member of the drop down list and then move away and back to the record concerned I get 'System.Data.DataRowView' in the display box. Chosing any of the other members of the drop down list and all works fine - it only occurs when I chose the first member of the drop down list.

Saving the changes from the dataset to the database whilst the 'System.Data.DataRowView' is showing updates the database with the correct field data (ie a number and not 'System.Data.DataRowView').

Be kind to me - Im only learning.

TIA.

Regards,

Glen Harvy

Connecting to SQL Server Express 2005 using C#

Cadence2.010-Jul-06 14:20

Cadence2.0

10-Jul-06 14:20

Hey Everyone,

Can you connect to SQL Server Express using C# or VB? I've been reading that SQL Server doesn't allow remote connections, but that does mean that I can't connect to a localhost database?

I'm trying to create an application that will connect to a db on the same system.

Thanks in advance!

Karthik Suspicious | :suss:

Re: Connecting to SQL Server Express 2005 using C#

Paul Conrad10-Jul-06 14:39

Paul Conrad

10-Jul-06 14:39

Karthik Gaekwad wrote:
I've been reading that SQL Server doesn't allow remote connections,

You have to configure it to allow remote connections. Use the configuration management tool that comes with SQL 2005 Express.

PJC

Which local datasource to use? [modified]

Rashar10-Jul-06 13:44

Rashar

10-Jul-06 13:44

Hello,

Looking for some advice on choosing a local data source for a call reporting project that will have approximately 25 end users using it. I have currently developed the app in vb.net.

As of now, I am just using SQL Express edition and if I am deploying the project, I would like to be able to quickly access the data on the end users PC. Also, i'm finding that if I make a change in any of my table definitions (such as adding or removing a column), it seems to clear the data in my tables.

On the other hand, if I use MS Access, I get quick access to the tables if need be and I can make changes to the table without worrying about loosing data.

Any suggestions from a maintenance / performance perspective on the 2 sources?

Thanks,

Rashar

-- modified at 19:45 Monday 10th July, 2006

Re: Which local datasource to use?

god_bless_the_world10-Jul-06 13:56

god_bless_the_world

10-Jul-06 13:56

I think SQL server is a better option.

In faith there is enough light for those who want to believe and enough shadows to blind those who don't.
- Blaise Pascal

Error with INSERT statement

leckey10-Jul-06 11:09

leckey

10-Jul-06 11:09

I have an asp.net project in which I pass a part number, and a part description to the stored proc.

What I want to insert (in table Parts)in the end is the part number, and the part type ID. I get that part type ID from table PartType.ID.

So my syntax is correct in that Enterprise manager makes no fuss, but when I actually do the insert I get the error "Cannot insert the value NULL into column 'PartTypeId',
table 'PartsDB.dbo.Parts'; column does not allow nulls.
INSERT fails. The statement has been terminated." I am not sure what is wrong with the code here.

CREATE PROCEDURE [dbo].[UpdatePart_Step_1]<br />
@PartNumber varchar (15),<br />
@PartType varchar (60) <br />
<br />
AS<br />
declare @pt_id integer<br />
<br />
if NOT EXISTS (Select * from Parts where PartNumber = @PartNumber)<br />
BEGIN<br />
<br />
SELECT  @pt_id = pt.ID FROM PartTypes pt <br />
<br />
INNER JOIN Parts p on pt.ID = p.PartTypeID<br />
<br />
WHERE pt.Description = @PartType<br />
<br />
insert into Parts<br />
(PartNumber, PartTypeID)<br />
<br />
VALUES<br />
(<br />
@PartNumber,<br />
@pt_id<br />
)<br />
END<br />
return 0<br />
GO

Does anyone see my problem?

Re: Error with INSERT statement

Colin Angus Mackay10-Jul-06 12:13

Colin Angus Mackay

10-Jul-06 12:13

I would guess that the SELECT @pt_id... is not returning any rows so @pt_id remains in its initial NULL state.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.