Search results matching tag 'SqlDataReader'

Creating a C# Web Service

BrianFan — Thu, 09 Apr 2009 04:00:00 GMT

I have an interesting problem at work. I need the ability to get an amount of data, maybe 15MB or less, on a recurring basis for external datasources. Such as from a system in another state. These data files, CSV, XML, or whatever are too large to be transfered via email, plus I would have to manually upload the data to my database, which I don't want to have to do on a daily basis.

So, I am considering 2 solutions. (1) Using RSS to pull a feed of data provided by the remote source into my application, then using a SqlBulkCopy or something to upload to my database. (2) Creating a web service that would essentially do the same thing, but put the data into object that I can create and manipulate before I insert the data into the database.

I would like to go with the web service route, but I know very little on how to actually do this. I know how to create a web service, but I have no idea who to set up the connection between my application on the remote data source. How does this work?

The main strategic advantage of the RSS feed is being to set a Time To Live (TTL) on it so that my application would get any updated data every minute or however long I set it to. Can something similar be established with a web service? Basicall I need to know how to set up a web service for my application that will access an external, geographically dispersed datasource, whether it be XML, CSV, SQL, or whatever is possible, pull that data into my application on a somewhat real time basis, and insert that data into my SQL Server 2005 database.

Any assistance would be greatly appreciated.

Thank you very much,

Brian

Conversion from oledb to sql

mhinkle2 — Sat, 21 Feb 2009 05:00:00 GMT

I have this code. Can someone help me convert this to work with a table called tblShipTo from the POSQL.mdf database

Protected Sub ShipTO_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ShipTO.SelectedIndexChanged

' Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|PO.mdb"

Dim SqlString As String = "SELECT * FROM tblShipTo WHERE ShipTo_ID = ?"

'If isnull(Me.txtPODate) Then Stop

If Me.txtPODate.Text = Nothing Then Me.txtPODate.Text = Format(Now, "Short date")

Using conn As New OleDbConnection(ConnString)

Using cmd As New OleDbCommand(SqlString, conn)cmd.Parameters.AddWithValue("", ShipTO.SelectedValue)

conn.Open()

Using reader As OleDbDataReader = cmd.ExecuteReader()

While reader.Read()

ShipAdd1.Text = reader("ShipAdd1")

ShipCity.Text = reader("ShipCity")

ShipState.Text = reader("ShipState")

ShipZip.Text = reader("ShipZip")

'\'Shi.Text = reader("State")

'VendorZIp.Text = reader("Zip")

End While

End Using

End Sub

SQLDataReader

mhinkle2 — Sat, 21 Feb 2009 05:00:00 GMT

I have the following code, but it is not working. I have tried for several days to get this to work, reading article after article but almost all articles only give partial answers not the whole answer and I cannnot get this work. Can someone tell me what is wrong.

Imports System.Data

Imports System.Text

Imports System.ComponentModel

Imports System.Data.SqlClient

Partial Class Default2

Inherits System.Web.UI.Page

Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim queryString As String = "SELECT * FROM dbo.tblShipTo;"

Dim connectionString As String

Using conn As New SqlConnection(connectionString)Dim command As New SqlCommand(queryString, conn)

conn.Open()

Dim reader As SqlDataReader = command.ExecuteReader()

While reader.Read()Console.WriteLine(String.Format("{0}, {1}", _

reader(0), reader(1)))

End While

reader.Close()

End Using

Dim queryString As New SqlCommand("Select * from tblShipTO", SqlConnection1)Dim drString As SqlDataReader

SqlConnection1.Open()

drString = queryString.ExecuteReader()

End Sub

End Class

getting SqlDataReader to work in VB

chase5465 — Wed, 18 Feb 2009 05:00:00 GMT

How do I get this to display the way I typed it (and see it in the "edit an existing message" box)? Greetings all. I am setting up a page that refers back to a table in SQL that stores the permissions for the various pages. I know ASP.NET has its own setup system, but I want something that I can manipulate easily offline. The table Userinfo has a list of "Users" and then two dozen or so boolean fields such as "Bags_Top" -- I want a procedure in the master page that will check the session username against the database, and only return the page "Bags_Top" if the attribute is TRUE in the table. I an using Visual Web Developer 2008 Express. I set up SQLDataSource1 with datasourcemode as datareader, and tested the connection and the query within, and they all work fine. Here is the code: codebehind (which works): " DataSourceMode="DataReader" EnableViewState="False" SelectCommand="SELECT [User], [Bags_Top] FROM [Userinfo] WHERE ([User] = @User)"> vb code (doesn't work at all): Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click Dim command As New SqlCommand Dim reader As SqlDataReader = Command.executereader() Dim str As String Str = "SELECT User, Password FROM Userinfo.dbo WHERE User = '" & TextBox1.Text & "'" Using SqlDataSource1 Try Dim command As New SqlCommand(Str, SqlDataSource1) Dim reader As SqlDataReader reader = command.ExecuteReader() reader.Read() reader.Close() Catch ex As Exception End Try End Using all the help files I can find talk about using this reader=command.executereader() command, but since the query ran fine when I tested it using the "Configure Data Source Wizard", I was wondering if there was a way to refer directly to the selectcommand without recoding it all, or if I have to recode it all, how to figure out the coding (which I have now been working on for a few days to no avail). if sqldatasource1 is set to dataset instead of datareader, would that change anything? thanks so much, - Chase

Storing Excel file into SQL Server Database

kssaranmca2006 — Wed, 31 Dec 2008 05:00:00 GMT

I need the code for storing Excel file into SQL Server Database. Any Third party control needs to store?

SqlDataReader Weird Locking (ASP.NET Issue?)

net_prog — Mon, 29 Dec 2008 05:00:00 GMT

Hello.

I have a problem using SqlDataReader. It does lock every and each other page when running.

I have one web-page with a long-running SqlDataReader. When I try to load another page at the same time which queries data from the database - that other page waits until SqlDataReader on the first page finishes working.

That is very weird since the pages use separate connections.

The code of the page-1:

public partial class misc_dbtest : System.Web.UI.Page
{
	protected void Page_Load(object sender, EventArgs e)
	{
	}
	protected void Button1_Click(object sender, EventArgs e)
	{
		SqlConnection con = new SqlConnection("server=MAIN;database=dbname;uid=sa;pwd=");
		SqlCommand cmd = new SqlCommand("select * from products", con);
		cmd.CommandType = CommandType.Text;
		con.Open();

		using (SqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
		{
			while (dataReader.Read())
			{
				Thread.Sleep(5);
			}
		}
	}
}

The page is actually simulates the long-run process to show the point.

Page-2 source:

public partial class misc_dbtest2 : System.Web.UI.Page
{
	protected void Page_Load(object sender, EventArgs e)
	{
	}
	protected void Button1_Click(object sender, EventArgs e)
	{
		SqlConnection con = new SqlConnection("server=MAIN;database=dbname;uid=sa;pwd=");
		SqlCommand cmd = new SqlCommand("SELECT * FROM sections", con);
		cmd.CommandType = CommandType.Text;
		con.Open();
		cmd.ExecuteNonQuery();
		con.Close();		
	}
}

So the page-2 waits for the page-1 to finish its work and only then returns. I think this should not be a case, since pages create separate connections. Could anyone help please, I spent much time searching for an answer, but didn't find anything valuable.

Thank you.

Extruct e string from one field end load it ot another in the same table

Dov — Tue, 23 Dec 2008 05:00:00 GMT

I am trying to use the CharIndex command to delimite the first and last posittions of a string in a text field in order to insert a copy of it in to another field ("image") in the same row of the table.

"SELECT ID,cod, texto, (CHARINDEX('ftnews', texto)+7) AS P1, (CHARINDEX('jpg', texto)-4) AS P2
FROM Artigos Order by ID asc "

I encounter difficulties to create the stored procedure

Appreciate any help

Dov Kruman

Strange DataReader problem - not retrieving data

DWesthead — Fri, 21 Nov 2008 05:00:00 GMT

Morning all,

I am having an issue with a SQLDataReader in a legacy app I am having to maintain, written in VB.NET 1.1.

For the past couple of days we have been having some datareaders in the application randomly stop returning data. I have stepped through the code and called the stored procedures manually with the data that is passed with the datareader and all results are coming back as expected. The first example below shows how we are doing this throughout the application:

1        Public Shared Function FindAssetsByAssetNumber(ByVal assetNumber As String, ByVal archived As String, ByRef dr As SqlDataReader, _
2            ByRef conn As SqlConnection, ByVal ssql As String) As Boolean
3            Dim cmd As SqlCommand
4            Dim emailAddresses As String
5
6
7            conn = New SqlConnection(ConfigurationSettings.AppSettings("DbConnectionString"))
8            conn.Open()
9            cmd = New SqlCommand(ssql, conn)
10           cmd.CommandType = CommandType.StoredProcedure
11           cmd.Parameters.Add("@AssetNumber", SqlDbType.VarChar, 10).Value = assetNumber
12           cmd.Parameters.Add("@Archived", SqlDbType.VarChar, 3).Value = archived
13
14           Try
15               dr = cmd.ExecuteReader
16           Catch ex As Exception
17               If Not dr.IsClosed Then dr.Close()
18               If conn.State <> ConnectionState.Closed Then conn.Close()
19               ErrorControl.HandleError(ex.Message)
20           End Try
21
22           Return True
23
24       End Function

This process still generally works and to clarify one point that we looked at already, the closing of the datareader and connection is in a catch block and not a finally because the calling code closes them if we are successful after they have been used.

When we run the code, no exception is generated but no data is returned either - has anyone seen this before, and does anyone have any ideas why this is happening as it has me stumped! :D

WHERE clause & AND Operator

bcoffey — Mon, 06 Oct 2008 04:00:00 GMT

I have two conditions in which both need to be met in order to be displayed on my datagrid, however, my SQL query is not returning my desired results. I have a bit column named Sailed in my tblTransit table and a StatusID in my tblWaybill table. They are joined together by a transitid. What I am trying to do is to display all the data in my tblWaybill table except if the Sailed bit is true, then filter out the rows with a statusid of 4 (which is "delivered"). Seems simple, but I cannot get the query to work properly, here is what I have tried:

SELECT tl.Sailed, wb.StatusID
FROM tblWayBills wb
LEFT JOIN tblLiteTransitList tl ON tl.TransitListID=wb.TransitListID
WHERE (tl.Sailed = 0) AND NOT wb.Statusid = 4

SELECT tl.Sailed, wb.StatusID
FROM tblWayBills wb
LEFT JOIN tblLiteTransitList tl ON tl.TransitListID=wb.TransitListID
WHERE (tl.Sailed = 0) AND (wb.Statusid != 4)

SELECT tl.Sailed, wb.StatusID
FROM tblWayBills wb
LEFT JOIN tblLiteTransitList tl ON tl.TransitListID=wb.TransitListID
WHERE ((tl.Sailed = 0) AND (wb.Statusid != 4))

It seems like no matter what I try, it is not meeting both conditions of my and operator in the where clause. All I want to do is display all statuses if the vessel hasn't sailed, but if the vessel has sailed, then don't display the delivered status (statusid=4)

Any ideas?

Re: Retreive single value from database in page load

pogo2346 — Sun, 05 Oct 2008 04:00:00 GMT

[quote user="MrSutton"]

hi,

I have a database with one table. --- CustomerID, CustomerName, CustomerEmail

I want to retreive the email string by CustomerID in my page load. I"m getting the CustomerID from the query string.

I Have created a stored procedure with a query that pulls the CustomerEmail by CustomerID.

I do not know how to get it in the page load.

Any help would be very much appreciated !

Thanx

Jean

[/quote]

// c# code
// in Page_Load event
connStr = "your db connection string;
SqlConnection cn = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand();
cmd.Connection=cn;
string sql = string.format(@"select email from customers where customer_id = '{0}',
customer_id);
cmd.CommandType = CommandType.Text;
//try and catch block would go here
cmd.CommandText = sql;

cn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
string email = rdr[0].ToString();