dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question???
dataset changes - back to sql question??? dataset changes - back to sql question???
dataset changes - back to sql question???
Go Back  Xtreme Visual Basic Talk > > > dataset changes - back to sql question???


Reply
 
Thread Tools Display Modes
  #21  
Old 12-21-2005, 01:51 PM
wayneph's Avatar
waynephdataset changes - back to sql question??? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default


The first thing I see is that your update statement is invalid. sel should be set. That will cause an error everytime, regardless of what else you're doing.
Code:
Public sqlString2 As String = "update part sel partnum = @partnum, partdescription = @partdescription, character09 = @character09, character07 = @character07 from part where classid = 'CRIB'"
As far as the vcUpdDataSet_PopulateVantage Method it will look more like this. (Assuming the above SQL.) Also double check the Database Types and Sizes. I just used the same as wthe Character09. You'll need to make sure it matches your database.
Code:
Dim sqlCmd As New SqlClient.SqlCommand(QueryCriteria, sqlConn) Dim param As SqlClient.SqlParameter param = da.UpdateCommand.Parameters.Add("@partnum", SqlDbType.NVarChar, 40, "partnum") 'Source Version for all of these wil be Current which is the default 'param.SourceVersion = DataRowVersion.Current param = da.UpdateCommand.Parameters.Add("@partdescription", SqlDbType.NVarChar, 40, "partdescription") param = da.UpdateCommand.Parameters.Add("@character09", SqlDbType.NVarChar, 40, "character09") param = da.UpdateCommand.Parameters.Add("@character07", SqlDbType.NVarChar, 40, "character07") param.SourceVersion = DataRowVersion.Original da.UpdateCommand = sqlCommand Try da.Update(ds, Table) Catch ex As Exception MessageBox.Show("Error Updating Data - " & ex.Message) End Try
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #22  
Old 12-21-2005, 03:06 PM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default Sorry, Same error continues

Thanks for looking at this.

Yes, I caught the sel vs set thing too. No changes though!

An unhandled exception of type 'System.NullReferenceException' occurred in vcLogic.exe

Additional information: Object reference not set to an instance of an object.

on

param = da.UpdateCommand.Parameters.Add("@partnum", SqlDbType.NVarChar, 40, "partnum")

Is the field length critical? In other words, If I had 255 charachers in that field, would 40 cause the error?

Is there anything else that would cause this error?
Reply With Quote
  #23  
Old 12-21-2005, 03:11 PM
wayneph's Avatar
waynephdataset changes - back to sql question??? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

I just saw a couple problems in my code. I tried typing it too fast... I was setting the parameters on the UpdateCommand instead of the SQLCommand object I created. That would have cause the error you mentioned.

Try this:
Code:
Dim sqlCmd As New SqlClient.SqlCommand(QueryCriteria, sqlConn) Dim param As SqlClient.SqlParameter param = sqlCmd.Parameters.Add("@partnum", SqlDbType.NVarChar, 40, "partnum") 'Source Version for all of these wil be Current which is the default 'param.SourceVersion = DataRowVersion.Current param = sqlCmd.Parameters.Add("@partdescription", SqlDbType.NVarChar, 40, "partdescription") param = sqlCmd.Parameters.Add("@character09", SqlDbType.NVarChar, 40, "character09") param = sqlCmd.Parameters.Add("@character07", SqlDbType.NVarChar, 40, "character07") da.UpdateCommand = sqlCommand Try da.Update(ds, Table) Catch ex As Exception MessageBox.Show("Error Updating Data - " & ex.Message) End Try

The the error you mentioned wouldn't be caused, by different sizes in the parameters. The only problems you'd see is if you passed "AD" as a number or something like that. If you try to pass 255 characters into a parameter with a length of 40, the data would just get truncated. It shouldn't error out.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #24  
Old 12-21-2005, 04:20 PM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default Okay!

Okay, we're making progress!

I know this because I am getting a new error:

Error Updating Data - Cannot insert duplicate key row in object 'part' with unique index 'partnum'. The statement has been terminated.

I view this as good news. The only concern is we are inserting a new row, rather than changing an existing row. There must be some minor thing wrong! Let me know what you think, and thanks for unraveling this mess for me!


Is this right:
da.UpdateCommand = sqlConnStr

Public Function vcUpdDataSet_PopulateVantage(ByVal ds As DataSet, ByVal QueryCriteria As String, ByVal Table As String)

Dim sqlConnStr As New SqlClient.SqlCommand(QueryCriteria, sqlConn)

'sqlConnStr.Connection = sqlConn
'sqlConnStr.CommandText = QueryCriteria

Dim param As SqlClient.SqlParameter
param = sqlConnStr.Parameters.Add("@partnum", SqlDbType.NVarChar, 40, "partnum")
param = sqlConnStr.Parameters.Add("@partdescription", SqlDbType.NVarChar, 40, "partdescription")
param = sqlConnStr.Parameters.Add("@character09", SqlDbType.NVarChar, 40, "character09")
param = sqlConnStr.Parameters.Add("@character07", SqlDbType.NVarChar, 40, "character07")
param.SourceVersion = DataRowVersion.Original
da.UpdateCommand = sqlConnStr
Try
da.Update(ds, Table)
Catch ex As Exception
MessageBox.Show("Error Updating Data - " & ex.Message)
End Try

End Function
Reply With Quote
  #25  
Old 12-22-2005, 08:26 AM
wayneph's Avatar
waynephdataset changes - back to sql question??? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

This goes back to what I thought you were doing. partnum should be in the where clause of your query, not what you're updating.

Since the only thing in the Where clause is where classid = 'CRIB', you are trying to update all of the records, and give them all the same part num.

But yes, we're making progress since you're actually getting an error back from an executed SQL Statement!

Try switching to the SQL Statement I suggested in post 17 here. I still think that's what you're really looking for. Make sure that you adjust your parameters to match what is in the SQL statement.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #26  
Old 12-22-2005, 09:15 AM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default More Progress!

Thanks, I did this and it worked. However, character09 is updating, character07 is not. Any idea why both fields would not update?
Reply With Quote
  #27  
Old 12-22-2005, 09:20 AM
wayneph's Avatar
waynephdataset changes - back to sql question??? wayneph is offline
Web Junkie

Retired Moderator
* Expert *
 
Join Date: Apr 2004
Location: D/FW, Texas, USA
Posts: 8,393
Default

Quote:
Originally Posted by Sanders720
Thanks, I did this and it worked. However, character09 is updating, character07 is not. Any idea why both fields would not update?
you have this on the character07 parameter: param.SourceVersion = DataRowVersion.Original

You're telling it to use the original version, not the current version. I'd set the partnum to the original, and leave the other two as Current.
__________________
-- wayne, MSSM Retired
> SELECT * FROM users WHERE clue > 0
0 rows returned
Reply With Quote
  #28  
Old 12-22-2005, 10:31 AM
Sanders720's Avatar
Sanders720 Sanders720 is offline
Centurion
 
Join Date: Nov 2002
Location: Minneapolis, MN
Posts: 121
Default Thanks

Thanks for all the great help on this. I believe I've got it!
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question??? dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question??? dataset changes - back to sql question???
dataset changes - back to sql question???
dataset changes - back to sql question???
 
dataset changes - back to sql question???
dataset changes - back to sql question???
 
-->