|
I am using VB.Net and have recently started working with databases. I hae managed to connect to and manipulate databases done in access that hae already been built, but I am currently trying to use a database I have built but get errors when I try to generate a dataset. The error reads "Retreiving the schema for OleDbDataAdapter1 failed. IErrorInfo.GetDescription failed with E_FAIL(0x80004005)" I think it has something to do with how I am building the database, all I hae done is create a new database and put a table in it with the headings that I require. I wold be greatfull if anyone cold tell me what else I need to do to the database to make it work.
Health Warning! Childlessness is hereditary (If your perents didn't have kids, neither will you!!)
|
|
|
|
|
I'm using SQL Express and I have a table that has a column of type image. My question is how to I go about inserting and/or updating an image in a row using C#? I'm aware that the image type in SQL is simply a BLOB, but I don't know how to insert an acutal picture (.jpg) using C#. Any and all help on this is very appreciated.
- Aaron
|
|
|
|
|
just check this article it helped me. www.codeproject.com/cs/database/ImageSaveInDataBase.asp
I hope it will help u too.
|
|
|
|
|
Hi,
I have put togther a windows 2003 server. I have SQL 2005 running and operating. I am having trouble connecting to my database from outside my network...I have port forwarding on tcp:1433. I have a public ip and domain services routing fine.
any suggestions?
Thanks,
Charles Pence
Student/DOTNET DEVELOPER
|
|
|
|
|
Do you have any firewalls enabled? Windows Server 2003 SP1 includes Windows Firewall which may need an exception added, if it's enabled (I'd recommend that it's turned on).
Also note that by default, TCP connections to some editions of SQL Server 2005 are not enabled. Go to Start, Programs, Microsoft SQL Server 2005, Configuration Tools, SQL Server Configuration Manager. Expand SQL Server 2005 Network Configuration, go to Protocols for instancename. Right-click TCP/IP and choose Enabled.
It's not generally considered a good idea to expose a SQL Server to the Internet. It would be better to implement some service to format the data more appropriately, for example, a web service.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Thank you kindly for the advice.
Thanks,
Charles Pence
|
|
|
|
|
Hello Dear
How to insert records into two tables using single Query in SQL Server
N.Rajakumar B.E.,
Application Developer,
www.newdreamz.tk
|
|
|
|
|
If you seperate the 2 SQL statements with a semicolon ; they will both be executed.
cmd.CommandText = "INSERT INTO Table1 VALUES(1,2,3); INSERT INTO Table2 VALUES(3,2,1);";
The inserts won't be transactional unless you run the statement in a transaction or add SQL to the statement to create one.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
I am trying to edit an exisiting row in an existing table. I know I have the row as I can output to debug window values and column (field) names. When I then try and loop thru my rows and update a specific column, the code runs fine but the edits never get into my table/dataset.
Row.State returns "2".
The table is simply a SQLServre table I created with Enterprise Manager.
I am just trying to update some rows (ints and strings) from a sub in vb.Net.
either I am accessing the table in a read only manner or I am missing the function to make the "comit" happen. Any help would be appreciated.
If you need to look at the code, it is below...
Private Sub playwithSql()
Dim sqlConn As New SqlConnection(sConnection) <----This is cool...
Dim mysqlCommand As New SqlCommand
mysqlCommand.Connection = sqlConn
mysqlCommand.CommandType = CommandType.Text
mysqlCommand.CommandText = "SELECT * FROM TestTable"
Dim dsTxA As New DataSet
Dim dataAdapter As New SqlDataAdapter
dataAdapter.SelectCommand = mysqlCommand
dataAdapter.Fill(dsTxA)
Dim table As DataTable
table = dsTxA.Tables(0)
Dim col As DataColumn
For Each col In table.Columns
Debug.WriteLine(col.ColumnName)
Next col
Dim myrow As DataRow
For Each myrow In table.Rows
myrow.BeginEdit()
'' myrow.Item(2) = Date.Now
myrow.Item(1) = "data"
MessageBox.Show(myrow.RowState)
myrow.EndEdit()
'' myrow.AcceptChanges()
Next
''dataAdapter.Update(dsTVA)
''sqlConn.Close()
Debug.WriteLine("-----------------------------------------------------------")
End Sub
***DETAILS
sqlSrv2000
DotNet 1.1 (VB.NET)
|
|
|
|
|
Hi,
When u call myrow.AcceptChanges() method on datarow it will reset the RowState to unchanged and hence when you call dataAdapter.Update(dsTVA) it does nit find any record to be updated in database.
Also before you call dataAdapter.Update(dsTVA) you have to assign appropriate Insert/Update/Delete command of DataAdapter object.
Regards,
Ritesh
|
|
|
|
|
I got it figured out. Since I was using the DataAdapter,it seems that in order to "post back" to the dataset you have created, the table in the database needs to have a Primary Key. I guess because since when you re using the dataAapter, the resultant dataset (in memory) needs to somehow map back to the original table and a primary key is needed. I didn't need to use a DataAdapter but I was trying to do it using the new cool stuff in DotNet.
Here is a complete sample. (I palgerized some of this fro the help...)
Notice you will need to return a connectin. I did this with my sconneciton function...
**********UPDATING AN EXISTIN ROW WITH THE DATA ADAPTER - SQLSERVER**********
(I needed a primary key.)
''This works!!!!
Public Sub UpdateExistingRow()
Dim myConnection As New SqlConnection(sConnection)
Dim mySqlDataAdapter As SqlDataAdapter
mySqlDataAdapter = New SqlDataAdapter("SELECT * FROM TestTable1", sConnection)
Dim myDataSet As DataSet = New DataSet
Dim myDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(mySqlDataAdapter)
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
mySqlDataAdapter.Fill(myDataSet, "TestTable1")
Dim table As DataTable
table = myDataSet.Tables(0)
Dim myDatarow As DataRow
myDatarow = table.Rows(0)
myDatarow.Item(1) = "Wednesday"
mySqlDataAdapter.Update(myDataSet, "TestTable1")
End Sub
|
|
|
|
|
Hi,
I try to call a SQL function which is generated my Id, but the problem is that the return value return 0. Also to be able to execute the function, I have to remove the commandtype definition of my code.
----------------------------------------------------------------
Code where my command line is build
////////////////////////////////////
<br />
public void ExecuteStoredProcedure(string pStoredProcedureName, <br />
object[] pReturnValue)<br />
{<br />
if(dbConnection.IsConnected())<br />
{<br />
bool outputValue = false;<br />
command.Parameters.Clear();<br />
try<br />
{ <br />
foreach(IDbDataParameter parameter in parameterCollection)<br />
{<br />
if(parameter.Direction != ParameterDirection.Input)<br />
outputValue = true;<br />
command.Parameters.Add(parameter); <br />
}<br />
command.CommandText = dbConnection.FormatStoredProcedureCall<br />
(pStoredProcedureName, <br />
command.Parameters);<br />
<br />
if(outputValue)<br />
{<br />
IDataReader reader = command.ExecuteReader();<br />
reader.Close(); <br />
<br />
int count = 0;<br />
<br />
foreach(IDbDataParameter parameter in command.Parameters)<br />
{<br />
Console.WriteLine("Parameter value:" + parameter.Value.ToString());<br />
<br />
if(parameter.Direction != ParameterDirection.Input)<br />
{<br />
pReturnValue[count] = parameter.Value;<br />
count++;<br />
}<br />
} <br />
}<br />
else<br />
command.ExecuteNonQuery();<br />
}<br />
catch(Exception e)<br />
{<br />
throw new DbException(ErrorMessage.INVALID_METHOD);<br />
} <br />
parameterCollection.Clear();<br />
}<br />
else<br />
throw new DbException(ErrorMessage.NOT_CONNECTED);<br />
}<br />
Code when I call my function who generate my Id
///////////////////////////////////////////////
public static string NewId(ICommand pCommand, int pModuleId)<br />
{<br />
string sqlStatement = "NewRecordId";<br />
object[] id = new object[1]; <br />
try<br />
{ <br />
pCommand.DefineParameter("pReturnValue", <br />
DbType.String, <br />
null, <br />
ParameterDirection.ReturnValue, <br />
30);<br />
pCommand.DefineParameter("pCurrentDate", <br />
DbType.DateTime, <br />
DateTime.Now);<br />
pCommand.DefineParameter("pNewId", <br />
DbType.String, <br />
Guid.NewGuid().ToString());<br />
pCommand.ExecuteStoredProcedure(sqlStatement, id); <br />
<br />
if (id[0].ToString().Length != 30)<br />
throw new DatabaseLayerException("Genearated Id doesn't have 30 digits.");<br />
}<br />
catch(Exception e)<br />
{<br />
throw new DatabaseLayerException("LCMS5000", e.Message);<br />
}<br />
<br />
return(pModuleId.ToString() + id[0].ToString());<br />
}
help will be appreciated.
Stephan Martin
|
|
|
|
|
Hi,
I installed SQL Server in INDIA and developed a ASP.Net Application.
In my application, there is one page 'Page1' with one report generated by Repeater.
I need list of all employees Joined after 01/11/2005. The date format is in Indian Standared.I used the following Query.
SELECT *FROM Employees WHERE DateOfJoin >= CONVERT(datetime,'01/11/2005',102)
Here the DateOfJoin field is also from DateTime datatype.
I am getting expected results here (INDIA).
I hosted the same thing in Austrialian Server. I am not getting expected results. I am getting results of Earliar employees also.
What's wrong in it ?
How to solve this problem.
Please let me know as earilar possible.
Your best friend,
Murthy.
|
|
|
|
|
Insead of hard-coding the format of the date, try using a parameterized query, that way the environment will sort out the formatting of the DATETIME variable.
CREATE PROC SelectEmployeesAfterGivenDate
@DATE DATETIME
AS
SELECT * FROM Employees WHERE DateOfJoin >= @Date
|
|
|
|
|
J4amieC wrote: using a parameterized query
Also helps stop sql injection attacks
PJC
|
|
|
|
|
I used similar to like that. Instead of creating Procedure, I used as in the following way.
Dim cnn as new SqlConnection(...)
Dim cmd as new SqlCommand("SELECT *FROM Employees WHERE DateOfJoin >= @JoinDate, cnn)
Dim dr as SqlDataReader
cmd.Parameters.Add(New SqlParameter("@JoinDate", toDate))
where toDate is '01/11/2005' IST
dr = cmd.ExecuteReader
Still not getting. Is it still wrong ?
Please revert back.
Your best friend,
Murthy.
|
|
|
|
|
Hello,
I've got a problem. The Task is to create a database for a special kind of report.
For the further description I call the template report and a data acquisition for a report survey.
Every report has got sections which can occurs many times in a survey and every survey has to be hold in different version. A report can consist of many hundred or thousand datafields and a survey can be even larger, because it can occurs many times.
A version of a survey may only have one new entry, then all fields except the one entered would be NULL. An operator can validate every new entry, so that a hold a validated version of the survey consisting of all validated fields from the different versions (where every field can only be validated in one version. if a field is validated in another version, it overwrites the validate state in the former validate fieldversion).
When I have twenty versions I want to be able to read the latest version (with all the previous data, where this version is NULL) or a previous version (where the fields which are NULL are filled with previous version data) or the validated version (consisting of perhaps all versions).
The next challenge is that a report can change every week. Perhaps there will be twenty new fields and e.g. twelve will be obsolete. A old survey may be updated to a new report version but it must be possible to get all obsolete data, when watching it with a old report version.
From these reports I generate fix tables in another database fill with only the importet values by the latest validated version. So in my database only a history will be stored and - if you want to look up a survey - a survey-report can be created.
The data in the fields can have different types. Some are text, others are int or double or bit, but fix for a special field.
Now I don't want to make tables for every report (there will be many reports) and every version of a report, because I would have a table for every repeatable part and every of these tables can have hundreds of columns. In the worst case only one or two fields will be fields in a survey-version and the report-version won't be compatible when I drop or add some fields for a new version.
So I thought about a dynamic database. I can store every report-version and survey-version in a small set of tables. I would split the different value-types in different tables, so that the value can be found in different tables.
I could generate stored procedures on this, because the tables won't change. As only few people would work with it and the database is strictly seperated from the main database, I think I could live with the time a query will take longer and I will save lot of design time for every change.
But I don't know if I forgot something. Is it a bad idea? I talked to some database designers in our company. No one happy about such a solution (I ain't, too), but no one knows a better handable solution. But perhaps some of the board users know?
Thanks,
Ingo
------------------------------
A bug in a Microsoft Product? No! It's not a bug it's an undocumented feature!
|
|
|
|
|
...
I think I am going to side with your database designers.
Maybe just pass your user a copy of InfoPath?
|
|
|
|
|
DarkElv wrote: I think I am going to side with your database designers.
Yes, I'm too.
DarkElv wrote: Maybe just pass your user a copy of InfoPath?
InfoPath is not powerful enough. I got just the first part of the specification, but it seems not possible to integrate all functions. And InfoPath is to slow. There is to much data and after inserting a few replications of a repeatable sections it takes more than 20 seconds for an insertion.
I worked out a first part of the report and it's not possible to handle without taking a look at the other required functions.
Thanks,
Ingo
------------------------------
A bug in a Microsoft Product? No! It's not a bug it's an undocumented feature!
|
|
|
|
|
hi all,
i am completely new to working with data in .net.
i am using c#.
can anyone point me to a good tutorial to start with?
regards,
rnv
|
|
|
|
|
rnvrnv wrote: can anyone point me to a good tutorial to start with?
There are many high quality articles here on Code Project that can help you get started
PJC
|
|
|
|
|
Hey, anyone tested by how much does a Profiler trace slow down SQL Server?
|
|
|
|
|
I can't give you numbers, but I've seen it bring a SQL server down when it was close to capacity, so don't profile when the system is close to failure. Also, only use it for relative ranking between batches, not for any absolute numbers.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Hi, how do you configure SQL "Server Log" (NOT transaction log/ldf) to recycle every now and then? Say, everytime when "Server Log" size hit 50MB?
Thanks.
|
|
|
|
|
what is oj, it is used with from clause while joining tables
please explain fully
thank u
|
|
|
|